O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:

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) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required