Microsoft® SQL Server 2008 R2 Unleashed

Using Dynamic SQL in Stored Procedures

SQL Server allows the use of the EXEC statement in stored procedures to execute dynamic SQL statements. This capability allows you to do things such as pass in object names as parameters and dynamically execute a query against the table name passed in, as in the following example:

This feature can be useful when you have to pass a variable list of values into a stored procedure. The string contains a comma-separated list of numeric values or character strings, just as they would appear inside the parentheses of an IN clause. If you are passing character strings, you need to be sure to put single quotation ...