Good Coding Practices and Tips for Dynamic SQL

Introduction

Writing dynamic SQL is a task that requires discipline to avoid losing control over your code. If you just go ahead, your code can become very messy, and be difficult to read, troubleshoot and maintain. In this section, we will look at how to avoid this. I will also discuss some special cases: how you can use sp_executesql for input longer than 4000 chars, and how to use dynamic SQL with cursors, and the combination of dynamic SQL and user-defined functions.

Use Debug Prints

When you write a stored procedure that generates dynamic SQL, you should always include a @debug parameter:

CREATE PROCEDURE dynsql_sp @par1 int,

@debug bit = 0 AS

IF @debug = 1 PRINT @sql

When you get a syntax error from the dynamic SQL, it can be very confusing, and you may not even discern where it comes from. And even when you do, it can be very difficult to spot the error only by looking at the code that constructs the SQL. Once the SQL code is slapped in your face, the error is much more likely to be apparent to you. So always include a @debug parameter and a PRINT!

Nested Strings

As I’ve already mentioned, one problem with dynamic SQL is that you often need to deal with nested string delimiters. For instance, in the beginning of this article, I showed you the procedure general_select2. Here it is again:

CREATE PROCEDURE general_select2 @tblname nvarchar(127),

@key varchar(10) AS

EXEC(‘SELECT col1, col2, col3

FROM ‘ + @tblname + ‘

WHERE keycol = ”’ + @key + ””)

(Again, I like to emphasise that this sort of procedure is poor use of dynamic SQL.)

SQL is one of those languages where the method to include a string delimiter itself in a string literal is to double it. So those four consecutive single quotes (””) is a string literal with the value of a one single quote (). This is a simple example; it can get a lot worse. If you work with dynamic SQL, you must learn to master nested strings. Obviously, in this case you can easily escape the mess by using sp_executesql instead – yet another reason to use parameterized statements. However, there are situations when you need to deal with nested quotes even with sp_executesql. For instance, earlier in this article, I had this code:

N’ WHERE LastUpdated BETWEEN @fromdate AND ‘

N’ coalesce(@todate, ”99991231”)’

Spacing and Formatting

Another thing to be careful with is the spacing as you concatenate the parts of a query. Here is an example where it goes wrong:

EXEC(‘SELECT col1, col2, col3

FROM’ + @tblname + ‘

WHERE keycol = ”’ + @key + ””)

See that there is a space missing after FROM? When you compile the stored procedure you will get no error, but when you run it, you will be told that the columns keycolcol1, col2, col3 are missing. And since you know that the table you passed to the procedure has these columns you will be mighty confused. But this is the actual code generated, assuming the parameters foo and abc:

SELECT col1, col2, col3

FROMfoo

WHERE keycol = ‘abc’

This is not a syntax error, because FROMfoo is a column alias to col3. And, yes, it’s legal to use a WHERE clause, even if there is no FROM clause. But since the columns cannot exist out of the blue, you get an error for that.

This is also a good example why you should use debug prints. If the code looks like this:

SELECT @sql =’ SELECT col1, col2, col3

FROM’ + @tblname + ‘

WHERE keycol = ”’ + @key + ””

IF @debug = 1 PRINT @sql

EXEC(@sql)

It would be much easier to find the error by running the procedure with @debug = 1. (Obviously, had we included the dbo prefix, this error could not occur at all.)

Overall, good formatting is essential when working with dynamic SQL. Try to write the query as you would have written it in static SQL, and then add the string delimiters outside of that. T-SQL permits you to embed newlines in string literals (as testified by the example above), so in contrast to VB, you don’t need a string delimiter on each line. An advantage of this is that your debug PRINT is easier to read, and in the case of a syntax error the line number in the error message may guide you.

You may prefer, though, to have a string terminator on each line. A tip in such case is to do something like this:

EXEC(‘ SELECT col1, col2, col3 ‘ +

‘ FROM ‘ + @tblname +

‘ WHERE keycol = ”’ + @key + ””)

As you see, I have a space after the opening single quote on each line to avoid syntax problems due to missing spaces.

Dealing with Dynamic Table and Column Names

Passing table and column names as parameters to a procedure with dynamic SQL is rarely a good idea for application code. As I’ve said, you cannot pass a table or a column name as a parameter to sp_executesql, but you must interpolate it into the SQL string. Still you should protect it against SQL injection, as a matter of routine. It could be that bad it comes from user input.

To this end, you should use the built-in function quotename()quotename() takes two parameters: the first is a string, and the second is a pair of delimiters to wrap the string in. The default for the second parameter is []. Thus, quotename(‘Orders’) returns [Orders]quotename() takes care of nested delimiters, so if you have a really crazy table name like Left]Bracketquotename() will return [Left]]Bracket].

Note that when you work with names with several components, each component should be quoted separately. quotename(‘dbo.Orders’) returns [dbo.Orders], but that is a table in an unknown schema of which the first four characters are dbo and a dot. If you only work with the dbo schema, best practice is to add dbo in the dynamic SQL and only pass the table name. If you work with different schemas, pass the schema as a separate parameter. (Although you could use the built-in function parsename() to split up a @tblname parameter in parts.)

While general_select still is a poor idea as a stored procedure, here is nevertheless a version that summarises some good coding virtues for dynamic SQL:

CREATE PROCEDURE general_select @tblname nvarchar(128),

@key varchar(10),

@debug bit = 0 AS

DECLARE @sql nvarchar(4000)

SET @sql = ‘SELECT col1, col2, col3

FROM dbo.’ + quotename(@tblname) + ‘

WHERE keycol = @key’

IF @debug = 1 PRINT @sql

EXEC sp_executesql @sql, N’@key varchar(10)’, @key = @key

  • I’m using sp_executesql rather than EXEC().
  • I’m prefixing the table name with dbo.
  • I’m wrapping @tblname in quotename().
  • There is a @debug parameter.

Quotename, Nested Strings and Quotestring

The main purpose of quotename() is to quote object names, which is why the default for the second parameter is brackets. But you can specify other delimiters as well, including single quotes, which means that any single quote in the input is doubled. Thus, if you for some reason prefer to use EXEC(), you can use quotename() to protect yourself against SQL injection by help of this function. Here is an example.

IF @custname IS NOT NULL

SELECT @sql = @sql + ‘ AND custname = ‘ + quotename(@custname, ””)

Say that @custname has the value D’Artagnan. This part of the dynamic SQL becomes:

AND custname = ‘D”Artagnan’

There is a limitation with quotename(): its input parameter is nvarchar(max), so it does not handle long strings. A remedy is this user-defined function:

CREATE FUNCTION quotestring(@str nvarchar(max)) RETURNS nvarchar(max) AS

BEGIN

DECLARE @ret nvarchar(max),

@sq char(1)

SELECT @sq = ””

SELECT @ret = replace(@str, @sq, @sq + @sq)

RETURN(@sq + @ret + @sq)

END

Here is an example of using this function:

IF @custname IS NOT NULL

SELECT @sql = @sql + ‘ AND custname = ‘ + dbo.quotestring(@custname)

The result is the same as above.

QUOTED_IDENTIFIER

Another alternative to escape the mess of nested quotes, is make use of the fact that T-SQL has two string delimiters. To wit, if the setting QUOTED_IDENTIFIER is OFF, you can also use double quotes () as a string delimiter. The default for this setting depends on context, but the preferred setting is ON, and it must be ON to use XQuery, indexed views and indexes on computed columns. Thus, this is not a first-rate alternative, but if you are aware of the caveats, you can do this:

CREATE PROCEDURE general_select @tblname nvarchar(127),

@key key_type,

@debug bit = 0 AS

DECLARE @sql nvarchar(4000)

SET @sql = ‘SET QUOTED_IDENTIFIER OFF

SELECT col1, col2, col3

FROM dbo.’ + quotename(@tblname) + ‘

WHERE keycol = “‘ + @key + ‘”‘

IF @debug = 1 PRINT @sql

EXEC(@sql)

Since there are two different quote characters, the code is much easier to read. The single quotes are for the SQL string and the double quotes are for the embedded string literals.

Dynamic SQL in User-Defined Functions

This very simple: you cannot use dynamic SQL from user-defined functions written in T-SQL. This is because you are not permitted do anything in a UDF that could change the database state (as the UDF may be invoked as part of a query). Since you can do anything from dynamic SQL, including updates, it is obvious why dynamic SQL is not permitted.

I’ve seen more than one post on the newsgroups where people have been banging their head against this. But if you want to use dynamic SQL in a UDF, back out and redo your design. You have hit a roadblock, and in SQL 2000 there is no way out.

In SQL 2005 and later, you could implement your function as a CLR function. Recall that all data access from the CLR is dynamic SQL. (You are safe-guarded, so that if you perform an update operation from your function, you will get caught.) A word of warning though: data access from scalar UDFs can often give performance problems. If you say

SELECT … FROM tbl WHERE dbo.MyUdf(somecol) = @value

and MyUdf performs data access, you have created a hidden cursor.

Cursors and Dynamic SQL

Not that cursors are something you should use very frequently, but people often ask about using dynamic SQL with cursors, so I give an example for the sake of completeness. You cannot say DECLARE CURSOR EXEC(); you have to put the entire DECLARE CURSOR statement in dynamic SQL:

SELECT @sql = ‘DECLARE my_cur INSENSITIVE CURSOR FOR ‘ +

‘SELECT col1, col2, col3 FROM ‘ + @table

EXEC sp_executesql @sql

You may be used to using the LOCAL keyword with your cursors. However, it is important to understand that you must use a global cursor, as a local cursor will disappear when the dynamic SQL exits. (Because, as you know by now, the dynamic SQL is its own scope.) Once you have declared the cursor in this way, you can use the cursor in a normal fashion. You must be extra careful with error-handling though, so that you don’t exit the procedure without deallocating the cursor.

There is however a way to use locally-scoped cursors with dynamic SQL. You can achieve this with cursor variables, as in this example:

DECLARE @my_cur CURSOR

EXEC sp_executesql

N’SET @my_cur = CURSOR STATIC FOR

SELECT name FROM dbo.sysobjects;

OPEN @my_cur’,

N’@my_cur cursor OUTPUT’, @my_cur OUTPUT

FETCH NEXT FROM @my_cur

You refer to a cursor variable, just like named cursors, but there is an @ in front, and, as you see from the example, you can pass them as a parameters.