3

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.

  1. Why does the error appear, when the return types appear to be the same?
  2. If I drop/recreate the function, do all the stored procedures need to be recompiled?

1 Answer 1

7

A CLR TVF is a fundamentally different database object type than a SQL TVF so one must use 'DROP/CREATE' instead of ALTER to change the definition.

The type in sys.objects for a CLR TVF is 'FT' whereas a SQL TVF (not inline) is 'TF'.

2
  • That makes sense - thank you. Do I need to recompile all the procedures that utilise this function?
    – EvilDr
    Mar 9, 2018 at 13:24
  • 1
    @EvilDr, no explicit recompile is needed after the change. Procs/queries will be recompiled automatically upon next use. The interface of the old and new TVF is the same so the change is non-breaking.
    – Dan Guzman
    Mar 9, 2018 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.