Introduction

In my previous article

Dynamic SQL vs. Static SQL Part 1 – Security we looked at permissions

for dynamic and static SQL and security issues related to using dynamic SQL.

In this article I plan to show cases where you will be tempted to use

dynamic SQL even though static SQL solutions exist and are usually as good

if not better than the dynamic solution.

Many people choose to use dynamic SQL because it often requires less thought

to develop and deploy, requires less code and thus less time to type it, and

is thought to require less maintenance than its static SQL equivalent.

In this article we’ll explore these reasons and see if they are valid.

As with my previous article the focus of this one is to show you ways to

avoid using dynamic SQL. I’ll do

this by presenting dynamic SQL code and then showing you how to do the same

thing using static SQL. Dynamic

SQL should only be used when you can’t use static SQL.

Throughout this article I’ll use the Pubs database for my examples.

CASE 1 – Search Criteria

This first case is the one I have seen the most and is the best example I

know to demonstrate where the dynamic SQL

solution requires much less thought and less code to implement.

For this case, we’ll use the authors table in the pubs database.

We want to do a search for a particular author or authors in the table based

on the values we put into a variable.

Here is the code we’ll use in Query Analyzer:

DECLARE @names varchar(7000)
 
SET @names = 'white'
 
SELECT *
FROM authors
WHERE au_lname IN (@names)

Upon execution of the above code we get one row returned.

Now we want to search on more than one name.

Modify the SET statement in the above code to look like this:

SET @names = '''white'',''green'''

We have used the extra single quotes so that the names will look just like

they would if we had hard coded these values and not used a variable between

the parenthesis.

However, executing the code returns no rows.

This is because SQL server thinks this is one long string and tries to find

a last name ‘white’,’green’.

We will continue this case by stating that creating two or more separate

variables is not an option since we don’t want to limit the number of names

we can use by the number of variables we create; the only limit acceptable

is however long we make our one varchar variable.

The easiest solution from here is to convert the entire SELECT statement to

dynamic SQL. Execute this code:

DECLARE @names varchar(7000), @Query varchar(8000)
 
SET @names = '''white'',''green'''
 
SET @Query = '
SELECT *
FROM authors
WHERE au_lname IN (' + @names + ')'
 
PRINT @Query
 
EXEC (@Query)

This returns just what we want and will as long as the names variable is

formatted properly. However,

this solution will force you to deal with the issues I presented in my

previous article with respect to being forced to give SELECT permissions on

the authors table. The following

solution will do the same thing while avoiding dynamic SQL:

DECLARE @names varchar(7000), @len int, @CurPos int, @PrevPos int
 
CREATE TABLE #names (names varchar(35))
 
SET NOCOUNT ON
 
SET @names = 'white,green'
SET @len = LEN(@names) + 1
SET @CurPos = 1
SET @PrevPos = @CurPos
 
WHILE @CurPos < @len + 1
BEGIN
 
            IF SUBSTRING(@names + ',', @CurPos, 1) = ','
            BEGIN
 
                        INSERT INTO #names (names)
                        SELECT SUBSTRING(@names,@PrevPos,@CurPos - @PrevPos)
                       
                        SET @PrevPos = @CurPos + 1
            END
 
            SET @CurPos = @CurPos + 1
 
END
 
SET NOCOUNT OFF
 
SELECT *
FROM authors
WHERE au_lname IN (SELECT * FROM #names)
 
DROP TABLE #names

For those of you who like to check the speed between different solutions

that do the same thing you’ll notice these always run with only a few

milliseconds difference between them.

I’ll not go any further into speed as that is outside the scope of this

article. Another benefit you

gain when using this static SQL solution is that you can easily modify it to

allow more names than one varchar variable can hold by duplicating the loop

section as many times as needed for the number of names you plan to use.

As for the dynamic SQL, a varchar variable is limited to 8000 characters and

you won’t be able to use all 8000 for names since you must have room in

@Query for the code that will use the names to fetch the rows you want in

your result set.

Looking at the above two solutions we can see that the static SQL has more

code than the dynamic SQL. The

static SQL also took me longer to develop and I had to plan and test more to

get it right. When I first ran

it, I didn’t get the final name in the string of names until I added one to

the @len variable in the WHILE loop.

CASE 2 – Column Sorting

Let us suppose you want to deliver a result set sorted by a column specified

by a variable and you want to include some defaults with some of the sorts

but not all of them. Before I

learned how to use the CASE function in the ORDER BY clause I thought the

only way to achieve this was with dynamic SQL something like this:

DECLARE @Query varchar(500), @OrderBy varchar(10), @Sequence varchar(4)
 
SET @OrderBy = 'au_lname'
SET @Sequence = 'DESC'
 
SET @Query = '
SELECT *
FROM authors
ORDER BY ' + @OrderBy + ' ' + @Sequence
 
IF @OrderBy = 'au_lname' SET @Query = @Query + ', au_fname ' + @Sequence
 
PRINT @Query
 
EXEC (@Query)
 
This gets the job done nicely and is the quickest and simplest solution to implement.  The static SQL solution is longer and looks like this:
 
DECLARE @OrderBy varchar(10), @Sequence varchar(4)
 
SET @OrderBy = 'au_lname'
SET @Sequence = 'DESC'
 
SELECT *
FROM authors
ORDER BY CASE @OrderBy + @Sequence WHEN 'au_lnameASC' THEN au_lname WHEN 'au_fnameASC' THEN au_fname END ASC,
CASE @OrderBy + @Sequence WHEN 'au_lnameDESC' THEN au_lname WHEN 'au_fnameDESC' THEN au_fname END DESC,
CASE @OrderBy + @Sequence WHEN 'au_lnameASC' THEN au_fname END ASC,
CASE @OrderBy + @Sequence WHEN 'au_lnameDESC' THEN au_fname END DESC

This solution simply requires a little more planning and more code to

implement.

If you change @Sequence in both from DESC to ASC and/or @OrderBy

from ‘au_lname’ to ‘au_fname’

you will see that both examples give you the same result sets.

However, when both are placed into separate stored procedures and users are

given execute permissions, the dynamic SQL solution will fail unless the

users also have SELECT permissions on the authors table.

CASE 3 - Joins

For our last case let us suppose we plan to display author and/or title

information. The query will

display author information only if we send a value into @Author.

It will display title info only if we send a value into @Title.

Title and author info will be displayed only if we send values into both

variables. When we assign values

for both variables we will need 3 tables to get our results.

However, if we only send a value into one variable or the other we’ll only

need one table. Here is how the

dynamic SQL solution could look:

DECLARE @Query varchar(500), @Author varchar(20), @Title varchar(20)
 
SET @Author = ''
SET @Title = 'e'
 
SET @Query = '
SELECT *
FROM '
 
IF @Author <> '' SET @Query = @Query + 'authors a'
 
IF @Author <> '' AND @Title <> ''
            SET @Query = @Query + CHAR(13) + 'INNER JOIN titleauthor ta ON ta.au_id = a.au_id' + CHAR(13) + 'INNER JOIN '
 
IF @Title <> '' SET @Query = @Query + 'titles t'
 
IF @Author <> '' AND @Title <> ''
            SET @Query = @Query + ' ON t.title_id = ta.title_id'
 
IF @Author <> '' OR @Title <> '' SET @Query = @Query + CHAR(13) + 'WHERE '
 
IF @Author <> '' SET @Query = @Query + 'au_lname LIKE ''%' + @Author + '%'''
 
IF @Author <> '' AND @Title <> '' SET @Query = @Query + CHAR(13) + 'AND '
 
IF @Title <> '' SET @Query = @Query + 'title LIKE ''%' + @Title + '%'''
 
PRINT @Query
 
EXEC (@Query)

To do the same thing and avoid dynamic SQL we can use the following code:

DECLARE @Author varchar(20), @Title varchar(20)
 
SET @Author = 'a'
SET @Title = 'u'
 
IF @Author <> '' AND @Title = ''
BEGIN
 
            SELECT *
            FROM authors
            WHERE au_lname LIKE '%' + @Author + '%'
 
END
 
IF @Title <> '' AND @Author = ''
BEGIN
 
            SELECT *
            FROM titles
            WHERE title LIKE '%' + @Title + '%'
 
END
 
IF @Title <> '' AND @Author <> ''
BEGIN
 
            SELECT *
            FROM authors a
            INNER JOIN titleauthor ta ON ta.au_id = a.au_id
            INNER JOIN titles t ON t.title_id = ta.title_id
            WHERE au_lname LIKE '%' + @Author + '%'
            AND title LIKE '%' + @Title + '%'
 
END

Unless I didn’t test enough the above two solutions deliver the same result

set when the same values are sent in.

Please note, that without the fix described in my previous article a single

quote sent into the dynamic SQL will break the query and the dynamic SQL

will not be protected against unauthorized code being executed.

Static SQL Is Easier To Maintain

Unless you plan to give all users read and write permissions to an entire

database, static SQL will be easier to maintain.

This is because with dynamic SQL you will have to constantly add permissions

for each table or object used in the dynamic code.

With static SQL all you have to do is give execute

permissions to a stored procedure and it will work.

This reduced the thought and planning you must spend to ensure no one has

permissions they shouldn’t have and that users can only do what you want

them to and nothing more.

For many modifications, a find and replace will work just as well for the

dynamic code as for the static code.

So maintenance in this area is often the same.

Another benefit and time saver with static code is that SQL Server can

verify that your syntax is correct.

With dynamic SQL, SQL Server has no idea what code it will execute until you

execute it. There could be bugs

and errors waiting to surface as users begin using your dynamic SQL, unless

you spend extra time testing every possible combination for dynamic SQL

similar to the one used in CASE 3.

Testing increases geometrically with each variable you add and by the time

you have 20 or 30 variables that help determine how the dynamic SQL is

built, you must do a lot of testing to ensure you don’t build the string

incorrectly and cause a syntax error.

With static SQL you can build as many queries as needed and SQL Server will

check the syntax for you.

As your dynamic SQL code grows and the query it builds becomes more complex

the code becomes harder to follow and read.

Another problem appears when the code you put into a variable exceeds 8000

characters and gets chopped off abruptly.

At this point, you’re only alternative is to convert the dynamic SQL to

static SQL. The time needed for

conversion grows with the complexity of the queries being built.

I have faced and still face these problems.

A few months ago, I converted one of our most complex stored procedures from

dynamic SQL to static SQL. It

took about 3 weeks to convert and then a few more weeks to test to ensure no

functionality was lost.

Conclusion

When developing code you should always consider using static SQL.

Dynamic SQL only becomes a possible solution if you can find no way to use

static SQL. When you consider

coding time, permissions management, testing, readability, and the

possibility of being forced to convert code from dynamic to static you find

that maintenance requirements for static SQL are less than that for dynamic

SQL.