Incompatible object type when create and alter a table value function in SQL

Asked 28/1, 2014 at 22:55 Answered 23/10, 2021 at 17:2

sql sql-server sql-server-2008 sql-function create-function

I'm getting the below error for the given function.

Msg 2010, Level 16, State 1, Procedure GetTableFromDelimitedValues, Line 2 Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type.

IF NOT EXISTS(SELECT 1 FROM sys.objects 
              WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]'))
BEGIN
   EXECUTE('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues](@input varchar(max),
       @delimiter char(1) = ",")) RETURNS @Result TABLE (
       Value nvarchar(4000)) AS BEGIN RETURN END')
END
GO


ALTER FUNCTION [dbo].[GetTableFromDelimitedValues](
       @input varchar(max),
       @delimiter char(1) = ',')
RETURNS @Result TABLE
(
       Value nvarchar(4000)
)
AS
BEGIN
    DECLARE @position int;
    DECLARE @column nvarchar(4000);

    WHILE LEN(@input) > 0
    BEGIN
        SET @position = CHARINDEX(@delimiter, @input);
        IF (@position < 0) OR (@position IS NULL)
        BEGIN
            SET @position = 0;
        END

        IF @position > 0 
        BEGIN
            SET @column = SUBSTRING(@input, 1, @position - 1);
            SET @input = SUBSTRING(@input, @position + 1, LEN(@input) - @position)
        END
        ELSE
        BEGIN
            SET @column = @input;
            SET @input = '';
        END 

        INSERT @Result (Value) 
        SELECT @column;
    END;

    RETURN;                
END
GO

Can someone please help me to get the compatible type by fixing the function?

Blue answered 28/1, 2014 at 22:55 Comment(1)

Did one of the answers below solve this issue? – Tacita 23/8, 2018 at 16:0

You need to DROP and CREATE the function in this particular context

Since there is change in function return type, we must drop then recreate the function.

There are three types of functions,

Scalar
Inline table valued and
Multi Statement

ALTER cannot be used to change the function type.

Brandabrandais answered 8/7, 2014 at 8:36 Comment(0)

IF  EXISTS (SELECT [name] FROM sys.objects 
            WHERE object_id = OBJECT_ID('GetTableFromDelimitedValues'))
BEGIN
   DROP FUNCTION [GetTableFromDelimitedValues];
END
GO

/*  Now create function */
CREATE FUNCTION [dbo].[GetTableFromDelimitedValues](
       @input varchar(max),
       @delimiter char(1) = ',')
RETURNS @Result TABLE (
       Value nvarchar(4000)
)
AS
BEGIN
..
..
..
RETURN;
END

in OBJECT_ID function you need to pass only function name not the schema. and why would create it 1st and then Alter it . Just check for existence 1st if it exists then drop function and create your function as I have shown above.

Also do not add Type in where clause when checking for existence, if there is another object not a function but any other object with the same name, it will not pick it up in your select statement and you will end up creating a function with a name an object already exists (this will throw an error).

IF you want to do it your way this is how you would go about it

IF NOT EXISTS(SELECT 1 FROM sys.objects 
              WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]'))
BEGIN
   EXECUTE('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues]() RETURNS @Result TABLE (
       Value nvarchar(4000)) AS BEGIN RETURN END')
END
GO

Arrhenius answered 28/1, 2014 at 23:18 Comment(3)

Thank you for your reply. Initially I approached the problem in the same way as you suggested (Drop|Create) but it didn't go through as - Problem with DROP, could be some times executing user may not have rights to DROP in prod environments, hence to be on the safer side should go with ALTER. – Blue 28/1, 2014 at 23:30

@Blue have a look now I have updated my answer to fix the issue you had in your actual query. – Arrhenius 28/1, 2014 at 23:42

Thank you for your continued help. Sorry it didn't work and continued to show the same error. Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type. – Blue 29/1, 2014 at 0:18

In my case, this happened when I have a table name exactly as proc name. so making a change to proc name or a table referred in the proc should also fix this error message.

Dauntless answered 24/9, 2019 at 19:26 Comment(0)

I have something to notify on your error related to your code:
The error says Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type
Which means that you have to look on your lines after the ALTER....
And yes there t is:
@input varchar(max)
The SQL server 2008 r2 not accept objects varchar(MAX), but that is only if you run a stored procedure
Because if you create a table by hand then it is fully accept it.
If you want a large cell then type varchar(1024) or varchar(2048) both of them are accepted. I face this issue few days ago...
That is my humble opinion

ADDITIONAL CHANGES
Use this

IF NOT EXISTS(SELECT 1 FROM sys.objects 
WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]')) 
BEGIN 
execute('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues]( @input varchar(max), @delimiter char(1)= ",") RETURNS @Result TABLE ( Value nvarchar(4000)) AS BEGIN RETURN END')
END GO

.... Pay attention to the change from ' to the "

** ADDITIONAL CHANGES **

I use the following which also works fine... with no any issue...

IF  EXISTS (SELECT [name] FROM sys.objects 
            WHERE object_id = OBJECT_ID('GetTableFromDelimitedValues'))
BEGIN
   DROP FUNCTION [GetTableFromDelimitedValues];
END
BEGIN
   execute('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues]() 
    RETURNS 
    @Result TABLE (
    Value nvarchar(4000)) 
    AS 
    BEGIN 
    RETURN 
    END')
    execute('ALTER FUNCTION [dbo].[GetTableFromDelimitedValues](
       @input varchar(max),
       @delimiter char(1) = ",")
       RETURNS @Result TABLE (
       Value nvarchar(4000))
       AS 
    BEGIN 
    RETURN 
    END')
END
GO

Dowling answered 29/1, 2014 at 0:5 Comment(7)

Thank you for your help. Sorry it didn't work and continued to show the same error after removing max with 1024. – Blue 29/1, 2014 at 0:17

Try to see if the @delimiter char(1) = ',') syntax is the proper one... In any case the issue comes from this area. – Dowling 29/1, 2014 at 0:27

that works fine for me...

IF NOT EXISTS(SELECT 1 FROM sys.objects                WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]')) BEGIN    execute('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues](        @input varchar(max), @delimiter char(1)= ",")        RETURNS @Result TABLE (        Value nvarchar(4000)) AS BEGIN RETURN END') END GO

.... Pay attention to the change from ' to the " – Dowling 29/1, 2014 at 0:41

The execute for CREATE FUNCTION within IF NOT EXISTS block works fine, but I'm getting issue on the the followed ALTER FUNCTION where it says - Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type. – Blue 29/1, 2014 at 0:58

Hi< I have updated the original question with the complete code, the function will return a table values output for a coma delimited input. The IF block (where the EXECUTE for CREATE FUNCTION is written) works fine if I ran upto the end of IF block. This ensures the syntax is correct. But when I ran the complete script, it fails at ALTER FUNCTION saying Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type. – Blue 29/1, 2014 at 1:13

hank you for your reply. @Arrhenius also suggested the same. Initially I approached the problem in the same way (Drop|Create) but it didn't go through as - Problem with DROP, could be some times executing user may not have rights to DROP in prod environments, hence to be on the safer side should go with ALTER. – Blue 29/1, 2014 at 1:22

let us continue this discussion in chat – Dowling 29/1, 2014 at 1:27

I confirm the below code works. Seems the issue was somehow a scalar value function created with the same name during my development and got error as script's multi part table value alter statement function is compatible with it.

IF NOT EXISTS(SELECT 1 FROM sys.objects 
              WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]'))
BEGIN
   EXEC sp_executesql 
    @statement = N'CREATE FUNCTION dbo.[GetTableFromDelimitedValues] () RETURNS @Result 
    TABLE(Value nvarchar(4000))
    AS 
    BEGIN 
      RETURN 
    END' ;
END
GO

ALTER FUNCTION [dbo].[GetTableFromDelimitedValues](
    @input varchar(max),
    @delimiter char(1) = ',')
RETURNS @Result TABLE
(
    Value nvarchar(4000)
)
AS
BEGIN
    DECLARE @position int;
    DECLARE @column nvarchar(4000);

    WHILE LEN(@input) > 0
    BEGIN
        SET @position = CHARINDEX(@delimiter, @input);
        IF (@position < 0) OR (@position IS NULL)
        BEGIN
            SET @position = 0;
        END

        IF @position > 0 
        BEGIN
            SET @column = SUBSTRING(@input, 1, @position - 1);
            SET @input = SUBSTRING(@input, @position + 1, LEN(@input) - @position)
        END
        ELSE
        BEGIN
            SET @column = @input;
            SET @input = '';
        END 

        INSERT @Result (Value) 
        SELECT @column;
    END;

    RETURN;                
END
GO

Blue answered 29/1, 2014 at 19:28 Comment(0)

enter image description here

bug does function created and data return is not field define, just change after the table(add field) returns.
solution fix bug:

deleted function just
edit key word "Alter" => "Create"
F5 is created function is success

Facial answered 23/10, 2021 at 17:2 Comment(2)

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Gessner 24/10, 2021 at 5:41

Please don't include code as image. Prefer copy/paste – Trapezius 24/10, 2021 at 7:55

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags