Incompatible object type when create and alter a table value function in SQL
create-function, sql, sql-function, sql-server, sql-server-2008
Solution
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.
Problem
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?