Microsoft® SQL Server 2012 Unleashed

Passing Object Names as Parameters

In SQL Server 2012, 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 24.15 generates an error message when you try to create the stored procedure.

LISTING 24.15 Attempting to Create a Stored Procedure by Using a Parameter to Pass in a Table Name

Click here to view code image

CREATE  proc find_data @table varchar(128)asselect * from @tableGO/* outputMsg 1087, Level 16, State 1, Procedure find_data, Line 4 Must declare the table variable "@table".*/

As you can see, when the parameter is used in the FROM