Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dynamic SQL vs. Static SQL Part 2, Code

By Robert Marda,

Dynamic SQL vs

 

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.

 

Total article views: 12567 | Views in the last 30 days: 7
 
Related Articles
ARTICLE

Dynamic SQL vs. Static SQL Part 1 - Security

Sooner or later everyone who works with SQL Server hears that it is better to avoid dynamic SQL at a...

ARTICLE

How Dynamic SQL Can Be Static SQL Part 2

This article shows you how to use the CASE function to design single query solutions that have chang...

FORUM

Avoiding Dynamic Queries

Avoiding Dynamic Queries

FORUM

dynamic linked server query

dynamic linked server query

FORUM
Tags
performance tuning    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones