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
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
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.
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