Sql server® 2012 t-sql fundamentals

The 

sp_executesql

 Stored procedure

The sp_executesql stored procedure was introduced after the EXEC command. It is more secure and 

more flexible in the sense that it has an interface; that is, it supports input and output parame ters. Note 

that unlike EXEC, sp_executesql supports only Unicode character strings as the input batch of code. 

The fact that you can use input and output parameters in your dynamic SQL code can help you 

write more secure and more efficient code. In terms of security, parameters that appear in the code 

cannot be considered part of the code—they can only be considered operands in expressions. So, by 

using parameters, you can eliminate your exposure to SQL injection. 

The sp_executesql stored procedure can perform better than EXEC because its parameterization 

aids in reusing cached execution plans. An execution plan is the physical processing plan that SQL 

Server produces for a query, with the set of instructions regarding which objects to access, in what 

order, which indexes to use, how to access them, which join algorithms to use, and so on. To simplify 

things, one of the requirements for reusing a previously cached plan is that the query string be the 

same as the one for which the plan exists in cache. The best way to efficiently reuse query execution 

plans is to use stored procedures with parameters. This way, even when parameter values change, the 

query string remains the same. But if for your own reasons you decide to use ad-hoc code instead of 

stored procedures, at least you can still work with parameters if you use sp_executesql and therefore 

increase the chances for plan reuse. 

The sp_executesql procedure has two input parameters and an assignments section. You specify 

the Unicode character string holding the batch of code you want to run in the first parameter, which 

is called @stmt. You provide a Unicode character string holding the declarations of input and output 

parameters in the second input parameter, which is called @params. Then you specify the assign-

ments of input and output parameters separated by commas. 

The following example constructs a batch of code with a query against the Sales.Orders table. The 

example uses an input parameter called @orderid in the query’s filter.

DECLARE @sql AS NVARCHAR(100); 

 

SET @sql = N'SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE orderid = @orderid;'; 

 

EXEC sp_executesql 

  @stmt = @sql, 

  @params = N'@orderid AS INT', 

  @orderid = 10248;

This code generates the following output.

orderid     custid      empid       orderdate 

----------- ----------- ----------- ----------------------- 

10248       85          5           2006-07-04 00:00:00.000

www.it-ebooks.info

---