Microsoft® SQL Server 2008 R2 Unleashed

Passing Object Names as Parameters

In SQL Server 2008, if you pass an object name as a parameter to a stored procedure, SQL Server attempts to treat it as a table-valued parameter unless the object name is used either as an argument in a WHERE clause or in a dynamic SQL query. For example, the code in Listing 28.14 generates an error message when you try to create the stored procedure.

Listing 28.14 Attempting to Create a Stored Procedure by Using a Parameter to Pass in a Table Name

As you can see, when the parameter is used in the FROM clause, SQL Server expects it to be defined as a table variable. To use the value in the parameter as a table ...