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?
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,
ALTER cannot be used to change the function type.