Table variables did not work for me until I started using an alias for the table variable when I had to qualify column names of a table variable.

Look at the UPDATE @myTable statement, where the INNER JOIN refers to tblTable.Field1, and tblTable is the alias for table variable @myTable.  If you try to use the table variable name directly, as in INNER JOIN @myTable.Field1 ON ---, or in SELECT @myTable.Field1, you will get an error.

ALTER FUNCTION uf_Function()

RETURNS @passedTable     TABLE ( Field1 int, Field2 int, Field3 nvarchar(50) )

AS

BEGIN

    DECLARE @myTable     TABLE (
                    Field1 int, Field2 int, Field3 nvarchar(50)
                    )

    INSERT INTO    @myTable
    SELECT    AcctBID, 0, Name
    FROM        AcctB

    UPDATE    @myTable
    SET        Field2 = CustTable.AcctAID
    FROM        @myTable tblTable INNER JOIN
            CustTable ON tblTable.Field1 = CustTable.AcctBID

        -- Plug the data into the table variable that gets passed back to the calling routine.
    INSERT INTO    @passedTable
    SELECT    Field1, Field2, Field3
    FROM        @myTable

    RETURN

END

If you follow the rule of using an alias when qualifying column names of table variables you should have no problem writing quite complex T-SQL using table variables (well, no problems with the table variables)

Why not just use temporary tables? For one thing -- table variables are created in memory, not written to the tempdb database, and are therefore MUCH faster.

Note: Table variables are a new feature of MS SQL 2000.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
My FAQ Archive

Email This FAQ To A Friend

My Archive

My FAQ Archive