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 as they would appear inside the parentheses of an IN clause. If you are passing character strings, be sure to put single quotes around the values, as shown in Listing 28.26.

Listing 28.26 Passing a Variable List of Values into a Stored Procedure

 create proc find_books_by_type @typelist varchar(8000) as exec ('select title_id, title = substring(title, 1, 40), type, price          from titles where type in ('        + @typelist + ') order by type, title_id') go set quoted_identifier off exec find_books_by_type "'business', 'mod_cook', 'trad_cook'" go title_id title                                    type         price -------- ---------------------------------------- ------------ -------- BU1032   The Busy Executive's Database Guide      business      19.9900 BU1111   Cooking with Computers: Surreptitious Ba business      11.9500 BU2075   You Can Combat Computer Stress!          business      2.9900 BU7832   Straight Talk About Computers            business      19.9900 MC2222   Silicon Valley Gastronomic Treats        mod_cook      19.9900 MC3021   The Gourmet Microwave                    mod_cook      2.9900 TC3218   Onions, Leeks, and Garlic: Cooking Secre trad_cook     20.9500 TC4203   Fifty Years in Buckingham Palace Kitchen trad_cook     11.9500 TC7777   Sushi, Anyone?                           trad_cook     14.9900 

When using dynamic SQL in stored procedures, you need to be aware of a few issues:

• The query plan for the dynamic SQL statement is not saved in cache memory as a stored procedure query plan normally would be. The query plan will have to be generated each time the procedure is executed.

• Any local variables that are declared and assigned a value in the constructed string within an EXEC statement will not be available to the stored procedure outside of the EXEC command. The lifespan of a local variable is limited to the context in which it is declared, and the context of the EXEC command ends when it completes. For a solution to passing values back out from a dynamic query, see the section "Using OUTPUT Parameters with sp_executesql" later in this chapter.

• Any local variables declared and assigned a value in the stored procedure can be used to build the dynamic query statement, but the local variables cannot be referenced by any statements within the EXEC string. The commands in the EXEC statement run in a different context from the stored procedure, and you cannot reference local variables declared outside the current context.

• Commands executed in an EXEC string execute within the security context of the user executing the procedure, not the user who created the procedure. Typically, if a user has permission to execute a stored procedure, that user will also have implied permission to access all objects referenced in the stored procedure that are owned by the same person who created the stored procedure. However, if a user has permission to execute the procedure, but hasn't explicitly been granted the permissions necessary to perform all the actions specified in the EXEC string, a permission violation will occur at runtime.

• If you issue a USE command to change the database context in an EXEC statement, it is in effect only during the EXEC string execution. It will not change the database context for the stored procedure (see Listing 28.27).

Listing 28.27 Changing Database Context in an EXEC Statement

 use pubs go create proc db_context as print db_name() exec ('USE Northwind print db_name()') print db_name() go exec db_context go pubs Northwind pubs 

Using sp_executesql

If you want to have the flexibility of dynamic SQL, but the persistence of a stored query plan, consider using sp_executesql in your stored procedures instead of EXEC . The syntax for sp_executesql is as follows :

 sp_executesql @  SQL_commands,  @  parameter_definitions, param1,...paramN  

sp_executesql operates just as the EXEC statement with regard to the scope of names, permissions, and database context. However, sp_executesql is more efficient when executing the same SQL commands repeatedly, and the only change is the values of the parameters. Because the SQL statement remains constant and only the parameters change, SQL Server is more likely to reuse the execution plan generated for the first execution and simply substitute the new parameter values. This saves the overhead from having to compile a new execution plan each time.

Listing 28.28 provides an example of a stored procedure that takes up to three parameters and uses sp_executesql to invoke the dynamic queries.

Listing 28.28 Invoking Dynamic Queries in a Procedure Using sp_executesql

 create proc find_books_by_type2 @type1 char(12),                                 @type2 char(12) = null,                                 @type3 char(12) = null as exec sp_executesql N'select title_id, title = substring(title, 1, 40),       type, price from pubs.dbo.titles where type = @type',       N'@type char(12)',       @type = @type1 if @type2 is not null     exec sp_executesql N'select title_id, title = substring(title, 1, 40),           type, price from pubs.dbo.titles where type = @type',           N'@type char(12)',           @type = @type2 if @type3 is not null     exec sp_executesql N'select title_id, title = substring(title, 1, 40),           type, price from pubs.dbo.titles where type = @type',           N'@type char(12)',           @type = @type3 go set quoted_identifier off exec find_books_by_type2 'business', 'mod_cook', 'trad_cook' go title_id title                                    type         price -------- ---------------------------------------- ------------ ------- BU1032   The Busy Executive's Database Guide      business     19.9900 BU1111   Cooking with Computers: Surreptitious Ba business     11.9500 BU2075   You Can Combat Computer Stress!          business      2.9900 BU7832   Straight Talk About Computers            business     19.9900 title_id title                                    type         price -------- ---------------------------------------- ------------ ------- MC2222   Silicon Valley Gastronomic Treats        mod_cook     19.9900 MC3021   The Gourmet Microwave                    mod_cook      2.9900 title_id title                                    type         price -------- ---------------------------------------- ------------ ------- TC3218   Onions, Leeks, and Garlic: Cooking Secre trad_cook    20.9500 TC4203   Fifty Years in Buckingham Palace Kitchen trad_cook    11.9500 TC7777   Sushi, Anyone?                           trad_cook    14.9900 

Note that the SQL command and parameter definition parameters to sp_executesql must be of type nchar , nvarchar , or ntext . Also, for the query plans to be reused, the object names must be fully qualified in the SQL command.

Using OUTPUT Parameters with sp_executesql

The important concept to remember about dynamic SQL is that it runs in a separate scope from the stored procedure that invokes it. This is similar to when a stored procedure executes another stored procedure. Because local variables are available only within the current scope, you cannot access a local variable declared in a calling procedure from within a nested procedure. Similarly, you cannot access a local variable declared outside the scope of a dynamic SQL statement. With stored procedures, you can work around this limitation by using input and output parameters to pass values into and out of a nested stored procedure.

If you use sp_executesql to execute dynamic SQL, you can use local variables to pass values both into and out of the dynamic SQL query. As described in the previous section, the second parameter to sp_executesql is a comma-separated list that defines the parameters you will be using within the dynamic SQL statement. Just like parameter definitions for a stored procedure, some of these parameters can be defined as output parameters. To get the values back out, define the parameter as an output parameter in the parameter list, and then specify the output keyword when passing the variable in the corresponding argument list for sp_executesql .

Listing 28.29 shows an example of a stored procedure that uses sp_executesql to execute a dynamic SQL query and return a value via an output parameter. You can use the parameters inside the dynamic SQL-like parameters inside a stored procedure. Any values assigned to output parameters within the dynamic SQL query will be passed back to the local variable in the calling procedure.

Listing 28.29 Using Output Parameters in sp_executesql

 create proc get_avg_price @dbname sysname,                           @type varchar(12) = '%' as declare @dsql nvarchar(500),         @avgval float /********************************************************* ** build the dynamic query using the @avg and @type as ** variables, which will be passed in via sp_executesql **********************************************************/ select @dsql = 'select @avg = avg(isnull(price, 0)) from '                 + @dbname+ '..titles '                 + 'where type like @type' --print @dsql /************************************************************* ** submit the dynamic query using sp_executesql, passing type **  as an input parameter, and @avgval as an output parameter **  The value of @avg in the dynamic query will be passed **  back into @avgval *************************************************************/ exec sp_executesql @dsql, N'@avg float OUT, @type varchar(12)',                    @avgval OUT, @type print 'The avg value of price for the titles table'       + ' where type is like ''' + @type       + ''' in the ' + @dbname + ' database'       + ' is ' + ltrim(str(@avgval, 9,4)) go exec get_avg_price @dbname = 'pubs',                    @type = 'business' go The avg value of price for the titles table where type is like 'business' in  the pubs database is 13.7300 exec get_avg_price @dbname = 'pubs',                    @type = DEFAULT go The avg value of price for the titles table where type is like '%' in the pubs  database is 11.8130 exec get_avg_price @dbname = 'bigpubs2000',                    @type = 'business' go The avg value of price for the titles table where type is like 'business' in  the bigpubs2000 database is 15.0988