SQL Server 2008 table variable error: Must declare the scalar variable "@RESULT".

Question

I'm using table values for the first time as a parameter to a function in SQL Server 2008. The code below produces this error:

Must declare the scalar variable "@RESULT".

Why?! I'm declaring it on the first line of the function!

        ALTER FUNCTION f_Get_Total_Amount_Due(

      @CUSTOMER_LIST   [tpCSFM_CUSTOMER_SET_FOR_MONEY] READONLY

    )
    RETURNS [tpCSFM_CUSTOMER_SET_FOR_MONEY]
    AS
    BEGIN


 --Prepare the return value, start with initial customer list
 DECLARE @RESULT AS [tpCSFM_CUSTOMER_SET_FOR_MONEY]
 INSERT INTO @RESULT SELECT * FROM @CUSTOMER_LIST

 --Todo: populate with real values
 UPDATE @RESULT SET tpCSAM_MONEY_VALUE = 100

 --return total amounts as currency
 RETURN @RESULT

 END

Answer 1

I don't believe the table-valued functions got any new features for the return value to be able to be a user-defined table type.

However, you can try: RETURNS @RESULT [tpCSFM_CUSTOMER_SET_FOR_MONEY] and remove your separate DECLARE.

I'll need to check it out when I get a chance to set up a test case.

Answer 2

Your declaration would fall into the 'Multi-statement table valued function' case:

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

Unfortunately the @return_variable cannot be declared as a table type, you'd have to copy the declaration of the TVP type into the function return table type.

Answer 3

This one compiles fine:

--Returns the name of the field to use to find results

ALTER FUNCTION f_Get_Total_Amount_Due(

    @CUSTOMER_LIST          [tpCSFM_CUSTOMER_SET_FOR_MONEY] READONLY

) RETURNS @RESULT TABLE( tpCSFM_CUS_ID uniqueidentifier PRIMARY KEY, tpCSFM_MONEY_VALUE MONEY NULL ) AS BEGIN

--Prepare the return value, start with initial customer list

INSERT INTO @RESULT SELECT * FROM @CUSTOMER_LIST

--Todo: populate with real values
UPDATE @RESULT SET tpCSFM_MONEY_VALUE = 100

--return total amounts as currency
RETURN

END