Introduction of Dynamic SQL

Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1):

Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1)

Ref:
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2

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.

Introduction to Dynamic SQL (Part 2):

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

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.
- See more at: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1#sthash.AOMhcydS.dpu

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.
- See more at: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1#sthash.AOMhcydS.dpuf

drop table #tempauth

Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1)