Using Table Variables in a Join

When I use SQL Server 2000 table-valued variables in the FROM clause of a SELECT statement, I get the error message Server: Msg 137, Level 15, State 2, Line 8 Must declare the variable '@MyCustomers'. Can I use table variables in a join, as Listing 2 shows?

SQL Server Books Online (BOL) doesn't thoroughly document the technique for using local table variables. But the answer to your question is, Yes—you can reference a local table variable (or, alternatively, a table-valued user-defined function—UDF) in a FROM clause. However, you must first alias the local table variable in the FROM clause. Listing 3 shows how you could rewrite your query to alias the local table variable so that you can properly reference the variable in the FROM clause.