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?

Did one of the answers below solve this issue? – Tab Alleman Aug 23 '18 at 16:00 — Tab Alleman, Aug 23 '18 at 16:00

score 36 · Answer 1 · answered Jul 08 '14 at 08:36

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.

score 5 · Answer 2 · edited Jun 27 '14 at 13:57

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

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. — ary, Jan 28 '14 at 23:30
@ary have a look now I have updated my answer to fix the issue you had in your actual query. — M.Ali, Jan 28 '14 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. — ary, Jan 29 '14 at 00:18

score 3 · Answer 3 · answered Sep 24 '19 at 19:26

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.

answered Sep 24 '19 at 19:26

sesy

75
8

Lefteris Gkinis · Answer 4 · 2014-01-29T01:16:55.130

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

edited Jan 29 '14 at 01:16

answered Jan 29 '14 at 00:05

Lefteris Gkinis

1,229
6
26
63

Thank you for your help. Sorry it didn't work and continued to show the same error after removing max with 1024. – ary Jan 29 '14 at 00:17
Try to see if the `@delimiter char(1) = ',')` syntax is the proper one... In any case the issue comes from this area. – Lefteris Gkinis Jan 29 '14 at 00: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 `"` – Lefteris Gkinis Jan 29 '14 at 00: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. – ary Jan 29 '14 at 00: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. – ary Jan 29 '14 at 01:13
hank you for your reply. @M.Ali 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. – ary Jan 29 '14 at 01:22
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/46290/discussion-between-lefteris-gkinis-and-ary) – Lefteris Gkinis Jan 29 '14 at 01:27

score 0 · Answer 5 · answered Jan 29 '14 at 19:28

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

score 0 · Answer 6 · edited Oct 24 '21 at 02:15

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

edited Oct 24 '21 at 02:15

Parisa.H.R

3,303
3
19
38

answered Oct 23 '21 at 17:02

namsdp

1
3

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](/help/how-to-answer). – Community Oct 24 '21 at 05:41
Please don't include code as image. Prefer copy/paste – Elikill58 Oct 24 '21 at 07:55

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

6 Answers6