0

I'm trying to make a multi-statement table valued function and failing due to what I think is a limitation of functions and CTEs.

The intended function defines an input parameter(@Param) and and output table(@ResultTable).

It then executes a complex insert statement involving a CTE into that table variable which is (of necessity) terminated by a semicolon.

I then attempt another complex update statement to that table variable involving a CTE and receive an error saying that "Must declare the scalar variable "@ResultTable". Apparently it has dropped out of scope somehow.

I have used this sort of pattern in the past, so my only thought is that the CTEs seem to limit the scope in some way. Is this a known limitation?

1

Using the following cobbled-together-example, I'm able to utilize 3 different CTE's to work against the output table variable. Perhaps you could identify the difference between your process and this simple example?

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[TEST_TVF_CTE]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
  DROP FUNCTION [dbo].[TEST_TVF_CTE]

GO 
CREATE FUNCTION [dbo].[TEST_TVF_CTE] (@sInputList VARCHAR(8000))
RETURNS @OutTable TABLE (item VARCHAR(8000))

BEGIN
    WITH FIRSTCTE
    AS (
        SELECT TOP 1 TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME
        )
    INSERT INTO @OutTable
    SELECT *
    FROM FIRSTCTE;

    WITH SECONDCTE
    AS (
        SELECT TOP 1 TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME DESC
        )
    INSERT INTO @OutTable
    SELECT *
    FROM SECONDCTE;

    WITH FINALCTE
    AS (
        SELECT TOP 1 'XYZ' AS TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME DESC
        )
    UPDATE a
    SET a.item = b.table_name
    FROM @OutTable a
    JOIN FINALCTE b ON 1 = 1

    RETURN
END
GO

SELECT *
FROM DBO.TEST_tvf_cte('...')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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