The sp_executesql command is more flexible than EXEC(<string>) because it has an interface, which supports both input and output parameters. This capability allows you to create query strings with arguments that can reuse execution plans more efficiently than EXEC. The components of sp_executesql are very similar to those of a stored procedure, with the difference being that you construct the code dynamically. Those components include: a batch of code; parameter declaration section; parameter assignment section. The syntax for using sp_executesql is as follows:

EXEC sp_executesql
  @stmt = <statement>, -- similar to proc's body
  @params = <params>,  -- similar to proc's params declaration
  <params assignment>  -- like in a procedure call

The @stmt parameter is the input dynamic batch, which can refer to input and output parameters. This section is similar to a stored procedure’s body except that @stmt can be constructed dynamically, whereas a stored procedure’s body is static. In fact, you might want to invoke sp_executesql from a stored procedure’s code, whereby you construct the dynamic batch based on user inputs to the stored procedure. The @params parameter is similar to the header of a stored procedure, where you define input/output parameters. In fact, the syntax for @params is identical to that of a stored procedure’s declaration section. You can even define default values to the parameters just as you can with a stored procedure. The @params string can also be constructed dynamically. Finally, the <params assignment> section is similar to the EXEC part of invoking a stored procedure, in which you assign values to the input/output parameters.

To demonstrate that sp_executesql plan management is superior to that of EXEC, I’ll use the same example I showed earlier when discussing EXEC:

DECLARE @i AS INT;
SET @i = 10248;

DECLARE @sql AS NVARCHAR(46);
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @oid;';

EXEC sp_executesql
  @stmt = @sql,
  @params = N'@oid AS INT',
  @oid = @i;

Notice that now, instead of concatenating the contents of @i, this code defines an input parameter called @oid. The code defines @oid as an integer input in the @params section, and it assigns the contents of @i from the calling batch to @oid in the <params assignment> section.

Before you invoke this code and examine the execution plans it generates, clear the execution plans from cache:

DBCC FREEPROCCACHE;

Run the dynamic code three times with the following three inputs to the @i variable: 10248, 10249, and 10250. Next, query sys.syscacheobjects and notice in the output shown in Table 4-4 that only one prepared plan was created and that it was reused three times:

SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
  AND sql NOT LIKE '%sys.%'
  AND sql NOT LIKE '%sp_executesql%';

(@oid AS INT)SELECT * FROM
dbo.Orders
WHERE OrderID = @oid

As a reminder, EXEC created three separate ad hoc plans in the same scenario. Now imagine production environments in which the same query pattern is invoked thousands or tens of thousands of times a day, or even more.

Another powerful capability of sp_executesql related to its support for an interface is that it lets you use output parameters to return values to a variable defined in the calling batch. This capability avoids the need to return data through temporary tables, and it results in more efficient code and fewer recompilations. The syntax for defining and using output parameters is identical to that of stored procedures. Namely, you need to declare the parameter specifying the OUTPUT clause and also specify the OUTPUT clause when assigning the parameter with a pointer to a variable defined in the calling batch. For example, the following simplistic code sample demonstrates how to return a value from the dynamic batch, through the output parameter @p to the outer batch’s variable @i:

DECLARE @sql AS NVARCHAR(12), @i AS INT

SET @sql = N'SET @p = 10;';

EXEC sp_executesql
  @stmt   = @sql,
  @params = N'@p AS INT OUTPUT',
  @p      = @i OUTPUT;

SELECT @i;

This code returns the output 10.

You can use sp_executesql’s output parameters in many interesting ways. For example, here’s a nifty trick I learned from Ron Talmage, who is a SQL Server MVP, a mentor, and a founder of Solid Quality Learning. Suppose that you have a character string stored in a variable called @s that holds hex digits that represent a binary string. You want to convert the character string to a real binary value and store it in a variable called @b. The task is actually much trickier than it seems. If you use simple conversions, you get the binary representation of each character, which is not really what you’re after. However, as Ron figured out, you can use sp_executesql’s output parameter to assign the string as if it were a binary value to a binary parameter, as demonstrated by the following code:

DECLARE @sql AS NVARCHAR(4000),
  @b AS VARBINARY(1000), @s AS VARCHAR(2002);
SET @s = '0x0123456789abcdef';

IF @s NOT LIKE '0x%' OR @s LIKE '0x%[^0-9a-fA-F]%'
BEGIN
  RAISERROR('Possible SQL Injection attempt.', 16, 1);
  RETURN;
END
SET @sql = N'SET @o = ' + @s + N';';
EXEC sp_executesql
  @stmt = @sql,
  @params = N'@o AS VARBINARY(1000) OUTPUT',
  @o = @b OUTPUT;

SELECT @b;

This code first checked for a possible SQL Injection attempt (the input string must be a valid binary string). The code then converted the character string (′0x0123456789ABCDEF′ in this example) to the binary value 0x0123456789ABCDEF. Unfortunately, you cannot use this technique to convert a binary value to a character string. SQL Server provides you with a scalar user-defined function (UDF) called fn_varbintohexstr to achieve this:

DECLARE @sql AS NVARCHAR(4000),
  @b AS VARBINARY(1000), @s AS VARCHAR(2002);
SET @b = 0x0123456789ABCDEF;
SET @s = sys.fn_varbintohexstr(@b);
SELECT @s;

Note

Note that the fn_varbintohexstr function is undocumented and unsupported.

This code converted the binary value 0x0123456789ABCDEF to the character string ′0x0123456789ABCDEF′.

As I mentioned earlier, one of the limitations of sp_executesql in SQL Server 2000 was that the input code string was practically limited to 4000 characters. This limitation is not relevant anymore because you can now provide sp_executesql with an NVARCHAR(MAX) value as input. Note that sp_executesql supports only Unicode input–unlike EXEC which supports both regular character and Unicode input. Earlier I showed code using the EXEC command to construct 10,000 PRINT statements. Here’s code that accomplishes the same thing using sp_executesql:

DECLARE @sql AS NVARCHAR(MAX), @i AS INT;
SET @sql = N'';
SET @i = 1;
WHILE @i <= 10000
BEGIN
  SET @sql = @sql + N'PRINT ' + CAST(@i AS NVARCHAR(10))
    + NCHAR(13) + NCHAR(10);
  SET @i = @i + 1;
END
EXEC sp_executesql @sql;

This code prints numbers in the range 1 through 10,000, each on a separate line.