Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Using Dynamic SQL in Stored Procedures

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

create proc get_order_data 
 (@table varchar(30), @column varchar(30), @value int)
as
declare @query varchar(255)
select @query = 'select * from ' + @table
          + ' where ' + @column
          + ' = ' + convert(varchar(10), @value)

EXEC (@query)

return

This feature is especially useful when you have to pass a variable list of values into a stored procedure. The string would contain a comma separated list of numeric values or character strings just ...