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.


Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Passing an array of values to SQL Server (Stored Procedure) without parsing/string manipulation (26 March 2007)

Efficient and Dynamic Server-Side Paging with T-SQL (23 March 2004)

Introduction to Dynamic SQL (Part 2) (27 June 2001)

Dynamic Cross-Tabs/Pivot Tables (12 March 2001)

Implementing a Dynamic WHERE Clause (14 January 2001)

Other Recent Forum Posts

Performance tuning (57m)

As I gain experience and get older, I'm working much slower, but producing better quality, but (4h)

Master DB 2019 problem (19h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

- Advertisement -