Introduction to Dynamic SQL (Part 1)

By Damian Maclennen on 20 June 2001 | Tags: Dynamic SQL

One of the most common questions we get involves Dynamic SQL. We have some articles that cover it but none that really start with the basics. So Merkin sat down and wrote this introduction to dynamic SQL. Using dynamic SQL you can put a SQL statement inside a variable and execute that statement. It's what you have to do when you're trying to run Select * from @TableName. Thanks Merkin!

Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.

In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.

Dynamic SQL on the client

If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:

dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = " 
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)

or slightly more elaborate

dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)

Or for a generic table viewer

dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)

In each case, you are building your sql statement as a string, then executing that statement against an active database connection.

Dynamic SQL in a stored procedure

Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.

Create Procedure GetArticle
	@ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
	Articles
WHERE
	ArticleID = @ArticleID

GO

However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:

Create Procedure GenericTableSelect
	@TableName VarChar(100)
AS
SELECT * 
FROM @TableName

GO

You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.

Create Procedure GenericTableSelect
	@TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM ' 
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO

Try that. That should do it.

The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.

The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.

The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.

That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.

Until then have fun.