Incompatible object type when creating and modifying a table value function in SQL

Question

I get the following error for this function.

Msg 2010, Level 16, State 1, Procedure GetTableFromDelimitedValues, Line 2 Cannot change to '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 help me get a compatible type by setting a function?

Answer 1

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

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

There are three types of functions,

• Scalar
• Built-in table with rating and
• Multi statement

ALTER cannot be used to change the type of function.

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 you only need to pass the function name, not the schema. and why create it 1st, and then Alter it. Just check for the presence of the 1st one, if it exists, and then cancel the function and create your own function, as I showed above.

Also, do not add Type to where where 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 that already exists (this will cause an error).

IF you want to do it your own way, here's how you do 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 my table name is exactly the same as the process name. therefore, changing the name of the proc or the table specified in proc should also fix this error message.

Answer 4

I have something to report about your error related to your code:
The error says Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type
This means you need to look at your lines after ALTER....
And yes, there are:
@input varchar(max)
SQL Server 2008 r2 does not accept varchar(MAX) objects varchar(MAX) , but that is only if you run the stored procedure
Because if you create the table manually, it fully accepts it.
If you want a large cell, enter varchar(1024) or varchar(2048) , both of which will be accepted. I come across this question a few days ago ...
This is my humble opinion.

ADDITIONAL CHANGES
Use it

 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 

.... Note the change from "to"

** ADDITIONAL CHANGES **

I use the following, which also works great ... without any problems ...

 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 

Answer 5

I confirm that the code below works. It seems that the problem was somehow a scalar value function created with the same name during my development, and got an error because the walter expression 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