Microsoft® SQL Server 2012 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:

Click here to view code image

IF EXISTS ( SELECT * FROM sys.procedures               WHERE schema_id= schema_id('dbo')                 AND name = N'get_order_data')   DROP PROCEDURE dbo.get_order_dataGOcreate proc get_order_data (@table varchar(30), @column varchar(30), @value int)asdeclare @query varchar(255)select @query = 'select * from ' + @table          + ' where ' + @column          + ' = ' + convert(varchar(10), @value) ...