I know the same question has been asked before, but the situation is slightly different and I'd like more explanation as to why it fails in my context please, when (AFAIK) the return type is the same.
Historically in my database I've used a CLR-based table valued function to split comma-separated values into a table containing an integer column.
Because SQL2016 introduces STRING_SPLIT, I need to swap out the CLR part of this function, and replace with the native TSQL function (as I have hundreds of stored procedures that rely on this function).
If I modify this function in SSMS, I see this:
ALTER FUNCTION [dbo].[e4fn_CreateIdTable](
@Ids [nvarchar](max),
@separator [nchar](1))
RETURNS TABLE (
[IdVal] [int] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ValueSplitter].[MyCompany.ValueSplitter].[SplitToIntegers]
I hoped I be able to alter it like so:
ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
@Ids [nvarchar](max),
@separator [nchar](1)
)
RETURNS TABLE
AS RETURN
(
SELECT
CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int) AS IdVal
FROM
STRING_SPLIT(@Ids, @separator)
)
GO
This returned the error:
Cannot perform alter on 'dbo.e4fn_CreateIdTable' because it is an incompatible object type.
I assumed that was because the table definition of the output parameter was not explicitly defined. So, I retried like this:
ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
@Ids [nvarchar](max),
@separator [nchar](1)
)
RETURNS @tbl TABLE (
IdVal int
)
AS
BEGIN
INSERT INTO @tbl (
Idval
)
SELECT
CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int)
FROM
STRING_SPLIT(@Ids, @separator);
RETURN;
END
GO
Again, the same error appears.
- Why does the error appear, when the return types appear to be the same?
- If I drop/recreate the function, do all the stored procedures need to be recompiled?