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
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
