If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do:

SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)

For all three examples you can expect someone to answer Use dynamic SQL and give a quick example on how to do it. Unfortunately, for all three examples above, dynamic SQL is a poor solution. On the other hand, there are situations where dynamic SQL is the best or only way to go.

In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages. To set the scene, I start with a very quick overview on application architecture for data access. I then proceed to describe the feature dynamic SQL as such, with a quick introduction followed by the gory syntax details. Next, I continue with a discussion on SQL injection, a security issue that you absolutely must have good understanding of when you work with dynamic SQL. This is followed by a section where I discuss why we use stored procedures, and how that is affected by the use of dynamic SQL. I carryon with a section on good practices and tips for writing dynamic SQL. I conclude by reviewing a number of situations where you could use dynamic SQL and whether it is a good or bad idea to do it.

The article covers all versions of SQL Server from SQL 6.5 to SQL 2005, with emphasis on SQL 2000 and SQL 2005.

Contents:

Note: many of the code samples in this text works against the pubs and Northwind databases that ship with SQL 2000 and SQL 7, but not with SQL 2005. You can download these databases from Microsoft's web site.

Accessing a Data from an Application

Before I describe dynamic SQL, I like to briefly discuss the various ways you can access data from an application to give an overview of what I'll be talking about in this article.

(Note: all through this text I will refer to client as anything that accesses SQL Server from the outside. In the overall application architecture that may in fact be a middle tier or a business layer, but as that is of little interest to this article, I use client in the sake of brevity.)

There are two main roads to go, and then there are forks and sub-forks.

1. Send SQL statements from the client to SQL Server.
   1. Rely on SQL generated by the client API, using options like CommandType.TableDirect and methods like .Update.
   2. Compose the SQL strings in the client code.
      1. Build the entire SQL string with parameter values expanded.
      2. Use parameterised queries.
2. Perform access through stored procedures.
1. Stored procedures in T-SQL
1. Use static SQL only.
2. Use dynamic SQL together with static SQL.
2. Stored procedures in a CLR language such as C# or VB .Net. (SQL 2005 only.)

Fork 1-a may be good for simple tasks, but you are likely to find that you outgrow it as the complexity of your application increases. In any case, this approach falls entirely outside the scope of this article.

Many applications are built along the principles of fork 1-b, and as long as you take the sub-fork 1-b-ii, it does not have to be bad. (Why 1-b-i is bad, is something I will come back to. Here I will just drop two keywords: SQL Injection and Query-Plan Reuse.) Nonetheless, in many shops the mandate is that you should use stored procedures. When you use stored procedures with only static SQL, users do not need direct permissions to access the tables, only permissions to execute the stored procedures, and thus you can use the stored procedure to control what users may and may not do.

The main focus for this text is sub-fork 2-a-ii. When used appropriately, dynamic SQL in stored procedures can be a powerful addition to static SQL. But some of the questions on the newsgroups leads to dynamic SQL in stored procedures that is so meaningless, that these people would be better off with fork 1-b instead.

Finally, fork 2-b, stored procedures in the CLR, is in many regards very similar to fork 1-b, since all data access from CLR procedures is through generated SQL strings, parameterised or unparameterised. If you have settled on SQL procedures for your application, there is little point in rewriting them into the CLR. However, CLR code can be a valuable supplement for tasks that are difficult to perform in T-SQL, but you yet want to perform server-side.

Introducing Dynamic SQL

In this chapter I will first look at some quick examples of dynamic SQL and point out some very important implications of using dynamic SQL. I will then describe sp_executesql and EXEC() in detail, the two commands you can use to invoke dynamic SQL from T-SQL.

A First Encounter

Understanding dynamic SQL itself is not difficult. Au contraire, it's rather too easy to use. Understanding the fine details, though, takes a little longer time. If you start out using dynamic SQL casually, you are bound to face accidents when things do not work as you have anticipated.

One of the problems listed in the introduction was how to write a stored procedure that takes a table name as its input. Here are two examples, based on the two ways to do dynamic SQL in Transact-SQL:

CREATE PROCEDURE general_select1 @tblname sysname,
                                 @key     varchar(10) AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT col1, col2, col3 ' +
              ' FROM dbo.' + quotename(@tblname) +
              ' WHERE keycol = @key'
EXEC sp_executesql @sql, N'@key varchar(10)', @key

CREATE PROCEDURE general_select2 @tblname nvarchar(127),
                                 @key     varchar(10) AS
EXEC('SELECT col1, col2, col3
      FROM ' + @tblname + '
      WHERE keycol = ''' + @key + '''')

Before I say anything else, permit me to point out that these are examples of bad usage of dynamic SQL. Passing a table name as a parameter is not how you should write stored procedures, and one aim of this article is to explain this in detail. Also, the two examples are not equivalent. While both examples are bad, the second example has several problems that the first does not have. What these problems are will be apparent as you read this text.

Whereas the above looks very simple and easy, there are some very important things to observe. The first thing is permissions. You may know that when you use stored procedures, users do not need permissions to access the tables accessed by the stored procedure. This does not apply when you use dynamic SQL! For the procedures above to execute successfully, the users must have SELECT permission on the table in @tblname. In SQL 2000 and earlier this is an absolute rule with no way around it. SQL 2005 provides alternative ways, something I will come back to in the section The Permission System.

Next thing to observe is that the dynamic SQL is not part of the stored procedure, but constitutes its own scope. Invoking a block of dynamic SQL is akin to call a nameless stored procedure created ad-hoc. This has a number of consequences:

• Within the block of dynamic SQL, you cannot access local variables (including table variables) or parameters of the calling stored procedure. But you can pass parameters – in and out – to a block of dynamic SQL if you use sp_executesql.
• Any USE statement in the dynamic SQL will not affect the calling stored procedure.
• Temp tables created in the dynamic SQL will not be accessible from the calling procedure since they are dropped when the dynamic SQL exits. (Compare to how temp tables created in a stored procedure go away when you exit the procedure.) The block of dynamic SQL can however access temp tables created by the calling procedure.
• If you issue a SET command in the dynamic SQL, the effect of the SET command lasts for the duration of the block of dynamic SQL only and does not affect the caller.
• The query plan for the stored procedure does not include the dynamic SQL. The block of dynamic SQL has a query plan of its own.

As you've seen there are two ways to invoke dynamic SQL, sp_executesql and EXEC(). sp_executesql was added in SQL 7, whereas EXEC() has been around since SQL 6.0. In application code, sp_executesql should be your choice 95% of the time for reasons that will prevail. For now I will only give two keywords: SQL Injection and Query-Plan Reuse. EXEC() is mainly useful for quick throw-away things and DBA tasks, but also comes to the rescue in SQL 2000 and SQL 7 when the SQL string exceeds 4000 characters. And, obviously, in SQL 6.5, EXEC() is the sole choice. In the next two sections we will look at these two commands in detail.

sp_executesql

sp_executesql is a built-in stored procedure that takes two pre-defined parameters and any number of user-defined parameters.

The first parameter @stmt is mandatory, and contains a batch of one or more SQL statements. The data type of @stmt is ntext in SQL 7 and SQL 2000, and nvarchar(MAX) in SQL 2005. Beware that you must pass an nvarchar/ntext value (that is, a Unicode value). A varchar value won't do.

The second parameter @params is optional, but you will use it 90% of the time. @params declares the parameters that you refer to in @stmt. The syntax of @params is exactly the same as for the parameter list of a stored procedure. The parameters can have default values and they can have the OUTPUT marker. Not all parameters you declare must actually appear in the SQL string. (Whereas all variables that appear in the SQL string must be declared, either with a DECLARE inside @stmt, or in @params.) Just like @stmt, the data type of @params in SQL 7/2000 is ntext and nvarchar(MAX) in SQL 2005.

The rest of the parameters are simply the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure, either positional or named. To get a value back from your output parameter, you must specify OUTPUT with the parameter, just like when you call a stored procedure. Note that @stmt and @params must be specified positionally. You can provide the parameter names for them, but these names are blissfully ignored.

Let's look at an example. Say that in your database, many tables have a column LastUpdated, which holds the time a row last was updated. You want to be able to find out how many rows in each table that were modified at least once during a period. This is not something you run as part of the application, but something you run as a DBA from time to time, so you just keep it as a script that you have a around. Here is what it could look like:

DECLARE @tbl    sysname,
        @sql    nvarchar(4000),
        @params nvarchar(4000),
        @count  int

DECLARE tblcur CURSOR STATIC LOCAL FOR
   SELECT object_name(id) FROM syscolumns WHERE name = 'LastUpdated'
   ORDER  BY 1
OPEN tblcur

WHILE 1 = 1
BEGIN
   FETCH tblcur INTO @tbl
   IF @@fetch_status <> 0
      BREAK

   SELECT @sql =
   N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
   N' WHERE LastUpdated BETWEEN @fromdate AND ' +
   N'                           coalesce(@todate, ''99991231'')'
   SELECT @params = N'@fromdate datetime, ' +
                    N'@todate   datetime = NULL, ' +
                    N'@cnt      int      OUTPUT'
   EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT
   PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END

DEALLOCATE tblcur

I've put the lines that pertain directly to the dynamic SQL in bold face. You can see that I have declared the @sql and @params variables to be of the maximum length for nvarchar variables in SQL 2000. In SQL 2005, you may want to make it a routine to make @sql nvarchar(MAX), more about this just below.

When I assign the @sql variable, I am careful to format the statement so that it is easy to read, and I leave in spaces to avoid that two concatenated parts are glued together without space in between, which could cause a syntax error. I put the table name in quotename() in case a table name has any special characters in it. I also prefix the table name with "dbo.", which is a good habit, as we will see when we look at dynamic SQL and query plans. Overall, I will cover this sort of good practices more in detail later in the text. Note also the appearance of '' around the date literal – the rule in T-SQL is that to include the string delimiter in a string, you must double it.

In this example, the dynamic SQL has three parameters: one mandatory input parameter, one optional input parameter, and one output parameter. I've assumed that this time the DBA wanted to see all changes made after 2006-01-01, which is why I've left out @todate in the call to sp_executesql. Since I left out one variable, I must specify the last, @cnt by name – the same rules as when you call a stored procedure. Note also that the variable is called @cnt in the dynamic SQL, but @count in the surrounding script. Normally, you might want to use the same name, but I wanted to stress that the @cnt in the dynamic SQL is only visible within the dynamic SQL, whereas @count is not visible there.

You may note that I've prepend the string literals with N to denote that they are Unicode strings. As @sql and @params are declared as nvarchar, technically this is not necessary (as long as you stick to your 8-bit character set). However, when you provide any of the strings directly in the call to sp_executesql, you must specify the N, as in this fairly silly example:

EXEC sp_executesql N'SELECT @x', N'@x int', @x = 2

If you remove any of the Ns, you will get an error message. Since sp_executesql is a built-in stored procedure, there is no implicit conversion from varchar.

You may wonder why I do not pass @tbl as a parameter as well. The answer is that you can't. Dynamic SQL is just like any other SQL. You can't specify a table name through a variable in T-SQL, that's the whole story. Thus, when you need to specify things like table names, column names etc dynamically, you must interpolate them into the string.

If you are on SQL 2000 or SQL 7, there is a limitation with sp_executesql when it comes to the length of the SQL string. While the parameter is ntext, you cannot use this data type for local variables. Thus, you will have to stick to nvarchar(4000). In many cases this will do fine, but it is not uncommon to exceed that limit. In this case, you will need to use EXEC(), described just below.

On SQL 2005, this is not an issue. Here you can use the new data type nvarchar(MAX) which can hold as much data as ntext, but without the many restrictions of ntext.

EXEC() takes one parameter which is an SQL statement to execute. The parameter can be a concatenation of string variables and string literals, but cannot include calls to functions or other operators. For very simple cases, EXEC() is less hassle than sp_executesql. For instance, say that you want to run UPDATE STATISTICS WITH FULLSCAN on some selected tables. It could look like this:

FETCH tblcur INTO @tbl
IF @@fetch_status <> 0 BREAK
EXEC('UPDATE STATISTICS [' + @tbl + '] WITH FULLSCAN')

In the example with sp_executesql, I used quotename(), but here I've let it suffice with adding brackets, in case there is a table named Order Details (which there is in the Northwind database). Since EXEC only permits string literals and string variables to be concatenated and not arbitrary expressions, this is not legal:

EXEC('UPDATE STATISTICS ' + quotename(@tbl) + ' WITH FULLSCAN')

Best practice is to always use a variable to hold the SQL statement, so the example would better read:

FETCH tblcur INTO @tbl
IF @@fetch_status <> 0 BREAK
SELECT @sql = 'UPDATE STATISTICS ' + quotename(@tbl) + ' WITH FULLSCAN'
EXEC(@sql)

The fact that you can concatenate strings within EXEC() can permit you to make very quick things, which can be convenient at times, but can lead to poor habits in application code. However, there are situations where this is an enormous blessing. As I mentioned, in SQL 7 and SQL 2000, you can in practice only use 4000 characters in your SQL string with sp_executesql. EXEC does not have this limitation, since you can say:

EXEC(@sql1 + @sql2 + @sql3)

Where all of @sql1, @sql2 and @sql3 can be 4000 characters long – or even 8000 characters as EXEC() permits you to use varchar.

Since you cannot use parameters, you cannot as easily get values out from EXEC() as you can with sp_executesql. You can, however, use INSERT-EXEC to insert the result set from EXEC() into a table. I will show you an example later on, when I also show you how you can use EXEC() to pass longer strings than 4000 characters to sp_executesql.

In SQL 2005, EXEC() permits impersonation so that you can say:

EXEC(@sql) AS USER = 'mitchell'
EXEC(@sql) AS LOGIN = 'CORDOBAMiguel'

This is mainly a syntactical shortcut that saves you from embedding the invocation of dynamic SQL in EXECUTE AS and REVERT. (I discuss these statements more in detail in my article Granting Permissions Through Stored Procedures.)

SQL 2005 adds a valuable extension to EXEC(): you can use it to execute strings on linked servers. I will cover this form of EXEC() in a separate section later in this text.

SQL Injection – a Serious Security Issue

Before you start to use dynamic SQL all over town, you need to learn about SQL injection and how you protect your application against it. SQL injection is a technique whereby an intruder enters data that causes your application to execute SQL statements you did not intend it to. SQL injection is possible as soon there is dynamic SQL which is handled carelessly, be that SQL statements sent from the client, dynamic SQL generated in T-SQL stored procedures, or SQL batches executed from CLR stored procedures. This is not a line of attack that is unique to MS SQL Server, but all RDBMS are open to it.

Here is an example. The purpose of the procedure below is to permit users to search for orders by various conditions. A real-life example of such a procedure would have many more parameters, but I've cut it down to two to be brief. (This is, by the way, a problem for which dynamic SQL is a very good solution.) As the procedure is written, it is open for SQL injection:

CREATE PROCEDURE search_orders @custid   nchar(5)     = NULL,
                               @shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
              ' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
   SELECT @sql = @sql + ' AND CustomerID LIKE ''' + @custid + ''''
IF @shipname IS NOT NULL
   SELECT @sql = @sql + ' AND ShipName LIKE ''' + @shipname + ''''
EXEC(@sql)

Before we look at a real attack, let's just discuss this from the point of view of user-friendliness. Assume that the input for the parameters @custid and @shipname comes directly from the user and a naïve and innocent user wants to look for orders where ShipName is Let's Stop N Shop, so he enters Let's. Do you see what will happen? Because @shipname includes a single quote, he will get a syntax error. So even if you think that SQL injection is no issue to you, because you trust your users, you still need to read this section, so that they can search for Brian O'Brien and Samuel Eto'o.

So this is the starting point. A delimiter, usually a single quote, affects your dynamic SQL, and a malicious user can take benefit of this. For instance, consider this input for @shipname:

' DROP TABLE Orders --

The resulting SQL becomes:

SELECT * FROM dbo.Orders WHERE 1 = 1  AND ShipName LIKE '' DROP TABLE orders --'

This is a perfectly legal batch of T-SQL, including the text in red. Of course, since there is something called permissions in SQL Server, this attack may or may not succeed. A plain user who runs a Windows application and who logs into SQL Server with his own login, is not likely to have permissions to drop a table. But it is not uncommon for web applications to have a general login that runs SQL queries on behalf of the users. And if this web app logs into SQL Server with sysadmin or db_owner privileges, the attack succeeds. Mind you, with sysadmin rights, the attacker can add users and logins as he pleases. And if the service account for SQL Server has admin privileges in Windows, the attacker has access into your network far beyond SQL Server through xp_cmdshell. (Which is disabled by default on SQL 2005, but if the attacker has achieved sysadmin rights on the server, he can change that.)

Typically, an attacker first tests what happens if he enters a single quote (') in an input field or a URL. If this yields a syntax error, the attacker knows that there is a vulnerability. He then finds out if he needs any extra tokens to terminate the query, and then he can add his own SQL statement. Finally he adds a comment character to kill the rest of the SQL string to avoid syntax errors. Single quote is the most common character to reveal openings for SQL injection, but if you have dynamic table and column names, there are more options an attacker could succeed with. Take this dreadful version of general_select:

CREATE PROCEDURE general_select2 @tblname nvarchar(127),
                                 @key     varchar(10) AS
EXEC('SELECT col1, col2, col3
      FROM ' + @tblname + '
      WHERE keycol = ''' + @key + '''')

and assume that @tblname comes from a URL. There are quite some options that an attacker could use to take benefit of this hole.

And don't overlook numeric values: they can very well be used for SQL injection. Of course, in a T-SQL procedure where the value is passed as an int parameter there is no risk, but if a supposedly numeric value is directly interpolated into an SQL string in client code, there is a huge potential for SQL injection.

Keep in mind that user input comes from more places than just input fields on a form. The most commonly used area for injection attacks on the Internet is probably parameters in URLs and cookies. Thus, be very careful how you handle anything that comes from the user.

You may think that it takes not only skill, but also luck for someone to find and exploit a hole for SQL injection. But remember that there are too many hackers out there with too much time on their hands. SQL injection is a serious security issue, and you must take precautions to protect your applications against it.

Thankfully, it is not difficult at all. I've seen mentioning of various ways to validate input data, but all that is a joke. There are three steadfast principles you need to follow:

• Never run with more privileges than necessary. Users that log into an application with their own login should normally only have EXEC permissions on stored procedures. If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions. A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions. Never let the web site log in as sa!
• For web applications: never expose error messages from SQL Server to the end user.
• Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().

The first point is mainly a safeguard, so that if there is a injection hole, the intruder will not be able to do that much harm. The second point makes the task for the attacker more difficult as he cannot get feedback from his attempts.

But it is the third point that is the actual protection, and that we will look a little closer at. The procedure search_orders above should be coded as:

CREATE PROCEDURE search_orders @custid   nchar(5) = NULL,
                               @shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
              ' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
   SELECT @sql = @sql + ' AND CustomerID LIKE @custid '
IF @shipname IS NOT NULL
   SELECT @sql = @sql + ' AND ShipName LIKE @shipname '
EXEC sp_executesql @sql, N'@custid nchar(5), @shipname nvarchar(40)',
                   @custid, @shipname

Since the SQL string does include any user input, there is no opening for SQL injection. It's as simple as that. By the way, note that since we can include parameters in the parameter list, even if they don't actually appear in the SQL string, we don't need any complicated logic to build the parameter list, but can keep it static. In the same vein, we can always pass all input parameters to the SQL string.

As you may recall, you cannot pass everything as parameters to dynamic SQL, for instance table and column names. In this case you must enclose all such object names in quotename(), that I will return to in the section Good Coding Practices and Tips for Dynamic SQL.

The example above was for dynamic SQL in a T-SQL stored procedure. The same advice applies to SQL generated in client code or in a CLR stored procedure. Since this is so important, here is an example of coding the above in VB6 and ADO:

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
                  " FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
   cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
   cmd.Parameters.Append
      cmd.CreateParameter("@custid", adWChar, adParamInput, 5, custid)
End If

If shipname <> "" Then
   cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
   cmd.Parameters.Append cmd.CreateParameter("@shipname", _
                         adVarWChar, adParamInput, 40, shipname)
End If

Set rs = cmd.Execute

Since the main focus of this text is dynamic SQL in T-SQL procedures, I will explain this example only briefly. In ADO you use ? as a parameter marker, and you can only pass parameters that actually appear in the SQL string. (If you specify too many parameters, you will get a completely incomprehensible error message.) If you use the SQL Profiler to see what ADO sends to SQL Server, you will find that it invokes – sp_executesql.

Protection against SQL injection is not the only advantage of using parameterised queries. In the section Caching Query Plans, we will look more in detail on parameterised queries and at a second very important reason to use them. This section also includes an example of composing and sending a parameterised SQL statement for SqlClient in VB .Net.

You may think that an even better protection against SQL injection is to use stored procedures with static SQL only. Yes, this is true, but! It depends on how you call your stored procedures from the client. If you compose an EXEC command into which you interpolate the input values, you are back on square one and you are as open to SQL injection as ever. In ADO, you need to call your procedure with the command type adCmdStoredProc and use .CreateParameter to specify the parameters. By specifying adCmdStoredProc, you call the stored procedure through RPC, Remote Procedure Call, which not only protects you against SQL injection, but it is also more efficient. Similar measures apply to other client APIs; all APIs I know of supply a way to call a stored procedure through RPC.

Dynamic SQL and Stored Procedures

In the introduction, I presented various strategies for data-access for an application, and I said that in many shops all data access is through stored procedures. In this section, I will look a little closer at the advantages with using stored procedures over sending SQL statements from the client. I will also look at what happens when you use dynamic SQL in a stored procedure, and show that you lose some of the advantages with stored procedures, whereas other are unaffected.

The Permission System

Historically, using stored procedures has been the way to give users access to data. In a locked-down database, users do not have permissions to access tables directly. Instead, the application performs all access through stored procedures that retrieve and update data in a controlled way, so that users only get to see data they have access to, and they cannot perform updates that violate business rules. This works as long as the procedure and the tables have the same owner, typically dbo (the database owner), through a mechanism known as ownership chaining.

As I have already mentioned, ownership chaining does not work when you use dynamic SQL. The reason for this is very simple: the block of dynamic SQL is not a procedure and does not have any owner. Thus the chain is broken.

SQL 2005

In SQL 2005 this can be addressed by signing a procedure that uses dynamic SQL with a certificate. You associate the certificate with a user, and grant that user (which is a user that cannot log in) the rights needed for the dynamic SQL to execute successfully. A second method in SQL 2005 is to use the EXECUTE AS clause to impersonate a user that has been granted the necessary permissions. This method is easier to use, but has side effects that can have unacceptable consequences for auditing, row-level security schemes and system monitoring. For this reason, my strong recommendation is to use certificates.

Describing these methods more closely, would take up too much space here. Instead I've written a separate article about them, Giving Permissions through Stored Procedures, where I discusses both certificates and impersonation in detail, and I also take a closer look on ownership chaining.

If you write CLR procedures that perform data access, the same is true for them. Ownership chaining never applies since all data access in a CLR procedure is through dynamic SQL. But you can use certificates or impersonation to avoid having to give users direct permissions on the tables.

SQL 2000 and earlier

On SQL 2000 there is no way to combine dynamic SQL with the encapsulation of permissions that you can get through stored procedures. Any use of dynamic SQL requires that the users have direct permissions on the accessed tables. If your security scheme precludes giving users permissions to access tables directly, you cannot use dynamic SQL. It is that plain and simple. Depending on the sensitivity of the data in the application, it may be acceptable to give the users SELECT permissions on the tables (or on some tables) to permit the use of dynamic SQL. I strongly recommend against granting users INSERT, UPDATE and DELETE rights on tables only to permit dynamic SQL in some occasional procedure.

There are nevertheless two alternatives, application roles and "application proxies", but they require you to change the application architecture, so it is nothing you introduce at whim.

Application roles were introduced in SQL 7. Users log into SQL Server but have no permissions on their own beyond the database access. Instead, the application activates the application role by sending a password somehow embedded into it, and this application role has permissions to read and update tables. With application roles, it does not really matter if you use stored procedures or not. The same is true for "application proxies" where the application authenticates the users outside SQL Server and logs into SQL Server on their behalf with a proxy login. This proxy login impersonates the users in SQL Server, and thus their permissions apply. However, since the users do not have any login, they cannot log into SQL Server outside the application. In Giving Permissions..., I discuss these two methods a little further.

For both ofr these methods, keep in mind about SQL injection, and don't grant your application role or the login-less users anything beyond SELECT, INSERT, UPDATE and DELETE permissions on tables. (And preferably only SELECT, and stick all updates into stored procedures with static SQL).

Every query you run in SQL Server requires a query plan. When you run a query the first time, SQL Server builds a query plan for it – or as the terminology goes – it compiles the query. SQL Server saves the plan in cache, and next time you run the query, the plan is reused. The query plan stays in cache until it's aged out because it has not been used for a while, or it is invalidated for some reason. (Why this happens falls outside the scope of this article.)

The reuse of cached query plans is very important for the performance of queries where the compilation time is in par with the execution time or exceeds it. If a query needs to run for four minutes, it does not matter much if the query is recompiled for an extra second each time. On the other hand, if the execution time of the query is 40 ms but it takes one second to compile the query, there is a huge gain with the cached plan, particularly if the query is executed over and over again.

Up to SQL 6.5 the only plans there were put into the cache were plans for stored procedures. Loose batches of SQL were compiled each time. And since the query plan for dynamic SQL is not part of the stored procedure, that includes dynamic SQL as well. Thus in SQL 6.5, the use of dynamic SQL nullified the benefit with stored procedures in this regard.

Starting with SQL 7, SQL Server also caches the plans for bare statements sent from a client or generated through dynamic SQL. Say that you send this query from the client, or execute it with EXEC():

SELECT O.OrderID, SUM(OD.UnitPrice * OD.Quantity)
FROM   Orders O
JOIN   [Order Details] OD ON O.OrderID = OD.OrderID
WHERE  O.OrderDate BETWEEN '19980201' AND '19980228'
  AND  EXISTS (SELECT *
               FROM   [Order Details] OD2
               WHERE  O.OrderID = OD2.OrderID
                  AND OD2.ProductID = 76)
GROUP  BY O.OrderID

The query returns the total order amount for the orders in February 1998 that contained the product Lakkalikööri. SQL Server will put the plan into the cache, and next time you run this query, the plan will be reused. But only if it is exactly the same query. Since the cache lookup is by a hash value computed from the query text, the cache is space- and case-sensitive. Thus, if you add a single space somewhere, the plan is not reused. More importantly, it is not unlikely that next time you want to run the query for a different product, or a different period.

All this changes, if you instead use sp_executesql to run your query with parameters:

DECLARE @sql nvarchar(2000)
SELECT @sql = 'SELECT O.OrderID, SUM(OD.UnitPrice * OD.Quantity)
               FROM   dbo.Orders O
               JOIN   dbo.[Order Details] OD ON O.OrderID = OD.OrderID
               WHERE  O.OrderDate BETWEEN @from AND @to
                 AND  EXISTS (SELECT *
                              FROM   dbo.[Order Details] OD2
                              WHERE  O.OrderID = OD2.OrderID
                                AND  OD2.ProductID = @prodid)
                GROUP  BY O.OrderID'
EXEC sp_executesql @sql, N'@from datetime, @to datetime, @prodid int',
                   '19980201', '19980228', 76

The principle for cache lookup is the same as for a non-parameterised query: SQL Server hashes the query text and looks up the hash value in the cache, still in a case- and space-sensitive fashion. But since the parameter values are not part of the query text, the same plan can be reused even when the input changes.

To make this really efficient there is one more thing you need to observe. Do you see that I've prefixed all tables in the query with dbo? There is a very important reason for this. On SQL 2000, this is an absolute must for effecient use of the query-plan cache. If you leave out dbo from a single table, each user will get his own copy of the plan in the cache. This is because on SQL 2000, each user has a default schema which is equal to the username. So when user1 runs a query that goes "SELECT ... FROM Orders", SQL Server must first check if there is a table user1.Orders, before it looks for dbo.Orders. Since user1.Orders could appear on the scene at any time, SQL Server needs to have a separate plan for each user.

The recommendation to include dbo applies very much to SQL 2005 as well, but it is not an absolute must. This is because on SQL 2005, owner and schema has been separated from each other so that users can have dbo as their default schema, in which case they can share a plan even when tables are not prefixed with dbo. (Since only Orders will be unambiguous.) Note here that for this to happen, users must have been created with the new command CREATE USER. If you – or the DBA – use sp_adduser out of habit, users still have their own default schema, and the situation is the same as on SQL 2000. Thus, to be safe, always prefix your tables with dbo in your dynamic SQL on SQL 2005 as well. (Unless, of course, you are actually using different schemas and not only the dbo schema, something which is a lot easier to do now. If you want to read more about owner/schema separation, there is a section on it in my article Granting Permissions through Stored Procedures.)

If you instead use stored procedures, it is not equally important to prefix tables with dbo. Microsoft still recommends that you do, but even if you don't, users with different default schema can share the same query plan.

From what I have said here, it follows that if you use dynamic SQL with EXEC() you lose an important benefit of stored procedures whereas with sp_executesql you don't. At least in theory. It's easy to forget that dbo, and if you leave it out in just a single place in the query, you will get as many entries in the cache for the query as there are users running it. Recall also that the cache is space- and case-sensitive, so if you generate the same query in several places, you may inadvertently have different spacing or inconsistent use of case. Also, since the cache lookup is by a hash value computed from the query text, I would assume that this is somewhat more expensive than looking up a stored procedure. In fact, under extreme circumstances, heavy use of dynamic SQL, can lead to serious performance degradation. Some of my MVP colleagues have observed systems with lots of memory (> 20 GB) when the plan cache has been so filled with plans for SQL statements, that there have been hash collisions galore, and the cache lookup alone could take several seconds. Presumably, the applications in question either did not use parameterised queries at all, or they failed to prefix tables with dbo.

So far, I've only talked about dynamic SQL in stored procedures. But in this regard there is very little difference to SQL statements sent from the client, or SQL statements generated in CLR procedures. The same rules apply: unparameterised statements are cached but with little probability for reuse, whereas parameterised queries can be as efficient as stored procedures if you remember to always prefix the tables with dbo. (And still with the caveat that the cache lookup is space- and case-sensitive.) Most client APIs implement parameterised queries by calling sp_executesql under the covers.

In the section on SQL Injection, I included an example on how to do parameterised queries with ADO and VB6. Here is an example with VB .Net and SqlClient:

cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = _
    " SELECT O.OrderID, SUM(OD.UnitPrice * OD.Quantity)" & _
    " FROM   dbo.Orders O " & _
    " JOIN   dbo.[Order Details] OD ON O.OrderID = OD.OrderID" & _
    " WHERE  O.OrderDate BETWEEN @from AND @to" & _
    "   AND  EXISTS (SELECT *" & _
    "                FROM   dbo.[Order Details] OD2" & _
    "                WHERE  O.OrderID = OD2.OrderID" & _
    "                  AND  OD2.ProductID = @prodid)" & _
    " GROUP  BY O.OrderID"

cmd.Parameters.Add("@from", SqlDbType.Datetime)
cmd.Parameters("@from").Value = "1998-02-01"

cmd.Parameters.Add("@to", SqlDbType.Datetime)
cmd.Parameters("@to").Value = "1998-02-28"

cmd.Parameters.Add("@prodid", SqlDbType.Int)
cmd.Parameters("@prodid").Value = 76

In contrast to ADO, SqlClient uses names with @ for parameters. The syntax for defining parameters is similar to ADO, but not identical. This article is long enough, so I will not go into details on how the Parameters collection works. Instead, I refer you to MSDN where both SqlClient and ADO are documented in detail. Whatever client API you are using, please learn how to use parameterised commands with it. Yes, there is a tone of desperation in my voice. I don't know how many posts I've seen on the newsgroups over the years where people build their SQL strings by interpolating the values from input fields into the SQL string, and thereby degrading the performance of their application, and worst of all opening their database to SQL injection.

... and just when you thought you were safe, I need to turn this upside down. Recall what I said in the beginning of this section, that if the query is going to run for four minutes, one second extra for compilation is not a big deal. And if that recompilation slashes the execution time from forty minutes to four, there is a huge gain. Most queries benefit from cached parameterised plans, but not all do. Say that you have a query where the user can ask for data for some time span. If the user asks for a summary for a single day, there is a good non-clustered index that can be used for a sub-second response time. But if the request is for the entire year, the same index would be a disaster, and a table scan is better. On SQL 2005 you can force a query to be recompiled each time it is executed by adding OPTION (RECOMPILE) to the end of the query, and thus you can still use sp_executesql to get the best protection against SQL injection. On SQL 2000 and earlier, it may in fact be better to interpolate critical parameters into the query string when you need to force recompilation each time.

For the sake of completeness, I should mention that SQL Server is able to auto-parameterise queries. If you submit:

SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = N'ALFKI'

SQL Server may recast this as

SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = @P1

so if next time you submit BERGS instead of ALFKI, the query plan will be reused. Auto-parameterisation comes in two flavours: simple and forced. Simple is the default and is the only option on SQL 2000 and earlier. With simple parameterisation, auto-parameterisation happens only with very simple queries, and, it seems, with some inconsistency. With forced parameterisation, SQL Server parameteries all queries that comes its way (with some exceptions documented in Books Online). Forced parameterisation is, in my opinion, mainly a setting to cover up for poorly designed third-party application that uses unparameterised dynamic SQL. For your own development you should not rely on any form of auto-parameterisation. (But in the situation you really a want a new query plan each time, you may have to verify that it doesn't happen when you don't want to.)

They say seeing is believing. Here is a demo that you can try on yourself, if you have SQL 2005. First create this database:

CREATE DATABASE many_sps 
go
USE many_sps
go
DECLARE @sql nvarchar(4000), 
        @x   int
SELECT @x = 200
WHILE @x > 0
BEGIN
   SELECT @sql = 'CREATE PROCEDURE abc_' + ltrim(str(@x)) + 
                 '_sp @orderid i