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

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.