Introduction to Dynamic SQL (Part 2)

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

In a follow up to the Introduction to Dynamic SQL we take you through some of the tricks and pitfalls of this technique. We'll cover Create Table, sp_executesql, permissions, scope and more.

Welcome to Part 2 of my Introduction to Dynamic SQL.

In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.

The IN Clause

The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.

If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".

So we try to write a stored proc around this

Create Procedure Search
	@strIDs VarChar(100)
AS

SELECT * 
FROM
	Products
WHERE
	ProductID in (@strIDs)

GO

Oooops! No Go.

This will work

Create Procedure Search
	@strIDs VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'

Exec ( @SQL)

GO

N.B. This can also be solved using a technique like this.

Aliases

Giving a table or column a dynamic alias is a use for dynamic SQL.

This will not work

Select UserName FROM Table as @Alias

This will

Exec('Select UserName FROM Table as ' @Alias) 

DDL

A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"

SQL Server will not allow this

Create Table @TableName (
	ID int NOT NULL Primary Key,
	FieldName VarChar(10)
	)

Once again, dynamic SQL to the rescue

Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)

Similarly, the code to create a database would look like this:

Exec('Create Database ' + @myDBName)

sp_executesql

sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.

This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.

An example

Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'

Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'

The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.

sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.

Try this from a database that is not Pubs

Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)

You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.

So you build the dynamic sql, then run it in Pub's copy of sp_executesql

I.E.

Declare @SQL nVarChar(1000)

Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'

Execute pubs.dbo.sp_executesql @sql

Permissions

When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.

Scope

When you run dynamic sql, it runs in it's own scope.

This

exec('set rowcount 3')

Select * from Authors

exec('set rowcount 0')

Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.

This would be solved by this

exec('set rowcount 3 Select * from Authors Set rowcount 0')

Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.

declare @i int
Exec ('Select @i = 1')

Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.

Create Table #tempauth(
	au_id VarChar(100),
	au_fname VarChar(100),
	au_lname VarChar(100)

)

declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)


Select * from #tempauth

drop table #tempauth

Summary

That wraps up my intro to dynamic SQL. I hope it is a little clearer now than it was before. Dynamic SQL is a very powerful tool to have in your arsenal as long as it doesn't substitute for bad application design. If you can avoid it with better data modelling, then that is the best way to go as your code will end up neater and generally faster.

Until next time

Happy coding.