As a simple example of using EXEC, the following code returns the count of rows from a given variable table name:

SET NOCOUNT ON;
USE Northwind;

DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128);
SET @schemaname = N'dbo';
SET @tablename = N'Orders';

DECLARE @objectname AS NVARCHAR(517);
SET @objectname = QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename);
EXEC(N'SELECT COUNT(*) FROM ' + @objectname + N';'),

Given the Orders table name as input in the Northwind database, this code returns 830. I constructed the code within the parentheses of the EXEC command, but note that only a string variable, a string literal, or a concatenation of string variables and/or string literals are allowed within the parentheses. You’re not allowed to invoke functions or use a CASE expression. For example, the following code, which attempts to invoke the QUOTENAME function within the parentheses to quote object names, fails:

DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128);
SET @schemaname = N'dbo';
SET @tablename = N'Order Details';
EXEC(N'SELECT COUNT(*) FROM '
     + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';'),

This code produces the following error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'QUOTENAME'.

So it’s a good practice to always construct the code in a variable, where such limitations don’t apply, and then provide the variable name as input to the EXEC command, as in:

DECLARE
  @schemaname AS NVARCHAR(128),
  @tablename AS NVARCHAR(128),
  @sql AS NVARCHAR(539);
SET @schemaname = N'dbo';
SET @tablename = N'Order Details';
SET @sql = N'SELECT COUNT(*) FROM '
  + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';'
EXEC(@sql);

This code returns the output 2155.

As I mentioned earlier, EXEC(<string>) has no interface. Its only input is a character string with the code that you want to invoke. Remember that a dynamic batch has no access to local variables defined in the calling batch. For example, the following code attempts to access a variable defined in the calling batch and fails:

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

DECLARE @sql AS VARCHAR(52);
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @i;';
EXEC(@sql);

This code produces the following error:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@i".

Using EXEC, if you want to access the variable, you have to concatenate its contents to the code string you’re constructing dynamically:

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

DECLARE @sql AS VARCHAR(52);
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = '
  + CAST(@i AS VARCHAR(10)) + N';';
EXEC(@sql);

Concatenating the contents of a variable to a code string imposes a security risk (SQL Injection) if the variable contains a character string. One of the measures that you can take to protect against SQL Injection is to limit the size of the code string you’re constructing to the minimum required length (52 in this example). Of course, in practice you don’t need dynamic SQL at all in such a situation. You could simply use static code and refer to @i in the filter, as in:

SELECT * FROM dbo.Orders WHERE OrderID = @i;

I’ve used this simple example just for demonstration purposes. Imagine that other sections of the code are constructed dynamically and cannot be used in a static query.

Concatenating the contents of a variable has its performance drawbacks. SQL Server will create a new ad-hoc execution plan for each unique query string even though the query pattern is the same. To demonstrate this, first clear the execution plans from cache:

DBCC FREEPROCCACHE;

Then run the dynamic code three times, assigning a different value to @i every time. Use the following three values: 10248, 10249, and 10250. Next query sys.syscacheobjects (or master.dbo.syscacheobjects in SQL Server 2000) using the following code, and then examine the execution plan information you get back, which is shown in Table 4-1:

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

SELECT * FROM dbo.Orders
WHERE OrderID = 10250;

SELECT * FROM dbo.Orders
WHERE OrderID = 10249;

SELECT * FROM dbo.Orders
WHERE OrderID = 10248;

(@1 smallint)SELECT * FROM
[dbo].[Orders]
WHERE [OrderID]=@1

The code invoked dynamically is safe for autoparameterization because the query filters a unique column. Hence, a parameterized plan was created (in the fourth row in the table) and reused. Nevertheless, a separate ad hoc plan was created for each different input so that it can be reused if an identical query string is invoked again. Generating multiple plans has its cost, of course.

In addition to supporting no input parameters in the dynamic batch, EXEC doesn’t support output parameters. By default, EXEC returns the output of a query to the caller. For example, the following code returns the distinct count of values in a given column of a given table:

DECLARE
  @schemaname AS NVARCHAR(128),
  @tablename  AS NVARCHAR(128),
  @colname    AS NVARCHAR(128),
  @sql        AS NVARCHAR(805);

SET @schemaname = N'dbo';
SET @tablename  = N'Orders';
SET @colname    = N'CustomerID';
SET @sql = N'SELECT COUNT(DISTINCT '
  + QUOTENAME(@colname) + N') FROM '
  + QUOTENAME(@schemaname)
  + N'.'
  + QUOTENAME(@tablename)
  + N';';

EXEC(@sql);

Given the CustomerID column and the Orders table in the Northwind database as inputs, the output you get is 89. However, things are trickier when you want to return the output to a variable in the calling batch. To achieve this, you must first insert the output to a target table using the INSERT EXEC syntax, and then retrieve the value from the table into the variable, as in:

DECLARE
  @schemaname AS NVARCHAR(128),
  @tablename  AS NVARCHAR(128),
  @colname    AS NVARCHAR(128),
  @sql        AS NVARCHAR(805),
  @cnt       AS INT;

SET @schemaname = N'dbo';
SET @tablename  = N'Orders';
SET @colname    = N'CustomerID';
SET @sql = N'SELECT COUNT(DISTINCT '
  + QUOTENAME(@colname) + N') FROM '
  + QUOTENAME(@schemaname)
  + N'.'
  + QUOTENAME(@tablename)
  + N';';

CREATE TABLE #T(cnt INT);
INSERT INTO #T
  EXEC(@sql);
SET @cnt = (SELECT cnt FROM #T);
SELECT @cnt;
DROP TABLE #T;

Remember that if you create a temporary table in a calling batch, it is visible to an inner dynamic batch. So you could also create a temporary table first, and insert the value into the temporary table within the dynamic batch using a plain INSERT statement:

DECLARE
  @schemaname AS NVARCHAR(128),
  @tablename  AS NVARCHAR(128),
  @colname    AS NVARCHAR(128),
  @sql        AS NVARCHAR(825),
  @cnt       AS INT;

SET @schemaname = N'dbo';
SET @tablename  = N'Orders';
SET @colname    = N'CustomerID';
SET @sql = N'INSERT INTO #T(cnt) SELECT COUNT(DISTINCT '
  + QUOTENAME(@colname) + N') FROM '
  + QUOTENAME(@schemaname)
  + N'.'
  + QUOTENAME(@tablename)
  + N';';

CREATE TABLE #T(cnt INT);
EXEC(@sql);
SET @cnt = (SELECT cnt FROM #T);
SELECT @cnt;
DROP TABLE #T;

In SQL Server 2000, EXEC had an advantage over sp_executesql in terms of supporting longer input code. Even though technically sp_executesql’s input code string was of an NTEXT datatype, you typically wanted to construct the code string in a local variable. However, you couldn’t declare a local variable with a large object type, so practically, query strings executed with sp_executesql were limited to the largest supported length of a Unicode character string (NVARCHAR), which was 4,000 characters. EXEC, on the other hand, supported a regular character (VARCHAR) input code string, allowing up to 8000 characters. Furthermore, EXEC supports a special functionality that allows you to concatenate multiple variables within the parentheses, each up to the maximum supported size of 8000 characters. For example, the following code constructs and invokes a code string that is longer than 8000 characters by concatenating three variables, generating an output with 7999 A characters and one B character:

DECLARE @sql1 AS VARCHAR(8000), @sql2 AS VARCHAR(8000), @sql3 AS VARCHAR(8000);
SET @sql1 = 'PRINT ''';
SET @sql2 = REPLICATE('A', 7999) + 'B';
SET @sql3 = ''';';
EXEC(@sql1 + @sql2 + @sql3);

However, this technique is very awkward, and it requires some acrobatics to construct code strings that are longer than 8000 characters. In SQL Server 2005, this technique is no longer necessary because you can provide the EXEC command with a variable defined as VARCHAR(MAX) or NVARCHAR(MAX) as input. Now the input string can be up to 2 GB in size, meaning over two billion characters in a VARCHAR(MAX) value, and one billion characters in NVARCHAR(MAX). For example, the following code generates a code string with 10,000 PRINT statements within it, producing a printout of all numbers in the range 1 through 10,000, each in a separate output line:

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

SQL Server 2005 introduces the EXEC AT syntax, which allows you to invoke dynamic pass-through code against a linked server. This enhancement addresses several shortcomings of SQL Server 2000 in terms of support for linked servers. In SQL Server 2000, if you wanted to invoke pass-through code against a linked server, using the target server’s dialect, you had to use the OPENQUERY table function. OPENQUERY has several limitations: the input query string must be static–it can’t be a variable, and it can’t accept input arguments. Furthermore, you invoke OPENQUERY in the FROM clause of an outer query, so the function must represent a table. This requirement is very limiting when you just want to invoke executable code against the target server; for example, DDL statements. Similar limitations apply to the OPENROWSET table function. For details about the OPENQUERY and OPENROWSET functions, please refer to Books Online.

All these limitations were addressed by the EXEC command in SQL Server 2005 by using the AT clause. To see the new capabilities, from a SQL Server 2005 instance, create a linked server to another SQL Server instance to which you have access, for example, a local instance of SQL Server Express. I will use a linked server called Dojo in my examples:

EXEC sp_addlinkedserver [Dojo], 'SQL Server';

The following example shows how you can use input parameters in the input string:

EXEC
(
 'SELECT ProductID, ProductName, UnitPrice
FROM Northwind.dbo.Products
WHERE ProductID = ?;', 3
) AT [Dojo];

I’m assuming in this example that the Northwind database exists in the target server, and permissions were set to allow access to the target linked server.

EXEC invokes a query against Dojo, which returns the product details of a specified product ID (3 in this case), producing the output shown in Table 4-2.

The question mark is replaced with the specified input value. The input value doesn’t have to be a constant; it can be a variable:

DECLARE @pid AS INT;
SET @pid = 3;
EXEC
(
 'SELECT ProductID, ProductName, UnitPrice
FROM Northwind.dbo.Products
WHERE ProductID = ?;', @pid
) AT [Dojo];

In fact, even the input code string can be a variable, which you construct dynamically:

DECLARE @sql AS NVARCHAR(MAX), @pid AS INT;

SET @sql =
 'SELECT ProductID, ProductName, UnitPrice
FROM Northwind.dbo.Products
WHERE ProductID = ?;'
SET @pid = 3;

EXEC(@sql, @pid) AT [Dojo];

Furthermore, EXEC doesn’t have to return a table result. The following example creates a table called T1 in tempdb at Dojo:

EXEC
(
 'USE tempdb;
IF OBJECT_ID(''dbo.T1'') IS NOT NULL
  DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
  keycol INT NOT NULL PRIMARY KEY,
  datacol VARCHAR(10) NOT NULL
);'
) AT [Dojo];

Remember that the AT clause allows you to invoke pass-through code at a specified linked server. The pass-through code is in the target server’s dialect, and the target server type is not limited to SQL Server. It can be any OLEDB or ODBC provider supported as a linked server.

To demonstrate code invoked against a non–SQL Server provider, apply the following:

Note

Note that by enabling RPC, you’re increasing the attackable surface area of the target server. I’m turning this setting on for the AccessNorthwind linked server so that the code samples that I’ll be demonstrating will work. For details about enabling RPC, please refer to the section "Security for Remote Servers" in Books Online (URL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/03bb3e21-f917-4463-892c-10b1dc13c53a.htm).

(I’ll assume you have the target database installed in the specified folder.) The following code demonstrates this:

EXEC sp_addlinkedserver
   @server = 'AccessNorthwind',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   -- @datasrc may wrap; should appear in one line
   @datasrc = 'c:program filesmicrosoft officeoffice11samples
orthwind.mdb';
GO
-- Remove default self-mapping added for all local logins
EXEC sp_droplinkedsrvlogin 'AccessNorthwind', NULL;
-- Add login mappings
EXEC sp_addlinkedsrvlogin
  'AccessNorthwind', 'false', '<specify_local_login_name_here>', Admin, NULL;
-- Allow RPC out
EXEC sp_serveroption 'AccessNorthwind', 'rpc out', true;

The following code invokes a TRANSFORM query against the AccessNorthwind linked server. The query pivots monthly counts of orders per year, including a filter on the EmployeeID column based on an input parameter, and it generates the output shown in Table 4-3:

EXEC
(
 'TRANSFORM Count(*) AS CountOfOrders
  SELECT  YEAR(OrderDate) AS OrderYear
  FROM Orders
  WHERE EmployeeID = ?
  GROUP BY YEAR(OrderDate)
  PIVOT MONTH(OrderDate);', 3
) AT AccessNorthwind;

If you’ll allow me to digress a bit, this reminds me of one of Steve Kass’s intriguing tricks for overcoming the fact that pivoting in SQL Server is not dynamic. Steve is the technical editor of this book and is well-versed in the principles of logic. Steve once suggested creating a linked server to an Access database, which in turn has a linked table pointing to a SQL Server table. You then issue a TRANSFORM pass-through query against the linked server, which queries your SQL Server table using full dynamic pivoting capabilities. I thought this was quite nifty and original!

Once you’re done, remember to drop the T1 table and the linked servers created in this section:

EXEC
(
 'USE tempdb;
IF OBJECT_ID(''dbo.T1'') IS NOT NULL
  DROP TABLE dbo.T1;'
) AT [Dojo];
EXEC sp_dropserver [Dojo];
EXEC sp_dropserver [AccessNorthwind];