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.
