The curse and
blessings of dynamic SQL
Erland
Sommarskog
Click Here for an updated
version of this article.
If you follow the various newsgroups on
Microsoft SQL Server, you often see people asking why they can't say:
SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)
In many cases someone says "use dynamic SQL" and with
a simple example shows how to do it. However they ever so often forget to tell
about the pitfalls of dynamic SQL.
In this article I will discuss the of use
dynamic SQL in stored procedures in MS SQL Server, and I will show that this is
a powerful feature that should be used with care. I first recapitulate why we
use stored procedures at all, before I explain the feature as such. I then look
at the conflicts between the virtues of stored procedures and the effects of
dynamic SQL. There are two sections of other general problems with dynamic SQL.
I conclude with discussing several cases where dynamic SQL if often given as a
solution, but not always is a good solution, and I provide alternative
strategies for these problems.
Contents:
Why stored
procedures?
Before we look at what dynamic SQL actually is, we
need to look at why we use stored procedures at all. You can write a complex
application where you send down pure SQL statement from the client or the middle
layer, without any stored procedures. We don't use stored procedures because
it's hip. We use it because there are several advantages.
1. The permission system
Stored procedure is the classic way of giving users
controlled access to data. Users should not have rights to perform SELECT,
INSERT, UPDATE or DELETE statements directly, because if they gain access to a
tool like Query Analyzer, they could do whatever they like. For instance, in the
personnel database they could easily increase their salary... With stored
procedures, users do not need direct permissions to the tables, since when a
procedure runs, the rights that apply are those of the procedure
owner.
Today, there exists a few more options. Rather than
granting access to the users, you could grant access to an application role that
requires a password to activate, and you would hide that password in the
application. Application roles became available with SQL7. Even safer is to use
a middle-layer server such as MTS, because in this case the users do not even
need to have any direct access to the SQL Server.
But if you are not using application roles or MTS,
the use of stored procedures is just as important as ever for security in SQL
Server.
2. Caching query plans
Another important reason to use stored procedure is
to improve performance. Up to version 6.5, SQL Server did never reuse query
plans for bare SQL statements, only for stored procedures. This means that the
first time you call a stored procedure, SQL Server will determine a query plan,
and as long as this plan remains in cache 每 and there is no event that triggers
a recompilation of the plan 每 the plan will be reused. If you have a statement
that you call twice a day, and which takes five minutes to run, it is not much
of an issue whether it takes two seconds extra to compile the plan each time.
But if you have a simple statement which you run many times, compiling the plan
may take as long as time as running the query. In this case, there can be
significant gain in performance with a stored procedure.
SQL7 and later versions are also able to cache plans
for SQL statements outside stored procedures under some circumstances.
Nevertheless, you have much greater certainty that plans will be cached with
stored procedures.
3. Minimizing network traffic
This is also a performance issue. Say that you have a
complex SELECT statement that runs over 50 lines, but only a few values in the
WHERE clause change between executions. Putting this in a stored procedure
reduces the number of bytes sent over the network considerably, and if there is
frequent traffic, the stored procedure may give a huge performance boost.
This is even more striking when you have a flow of
logic which comprises several SELECT/INSERT/UPDATE that depend on each other.
With a stored procedure, you can use temp tables or variables to keep all the
data floating around in the server. Were you to use bare SQL statements, you
would need to have data going back and forth between server and client or the
middle layer. (Actually this is not entirely true. You can still create temp as
bare SQL statements, and keep the data there. But then you need to be careful
with connection pooling and disconnected record sets.)
4. Using output parameters
If you want to execute an SQL statement that returns
a single row of values, you still need to return these as a result set if you
send bare SQL statements. With stored procedures you have the option as
receiving them as output parameters, which is considerably faster. Although for
a single query, the difference in negligible in absolute terms. But if you call
2000 times to say:
INSERT tbl (...) VALUES (...)
SET @key = @@identity
you are likely to make a considerable gain if you
have @key as an output parameter.
5. Encapsulating logic
This is not a question of security or performance,
but one of modularizing your code. By using stored procedures, you don't have to
bog down your client code with construction of SQL statements. However, nothing
says that you must use stored procedures for this. You could just as well write
procedures in your client code that build the SQL statements from the
parameters, although you may find that the SQL code is hidden in the syntax of
the host language.
There is one special case where this point is very
significant, to wit the case you have no other application than Query Analyzer.
That is, you are writing stored procedures to be used by admin
people.
6. Keeping track of what is used
In a complex system with hundreds of tables, you
often want to know where a certain table or column is referred to. For instance,
you may want to know what happens if you change a column. When you have all code
in stored procedures, you only need to search the procedure code to find the
references. Or simply build the database without the column or table you plan to
alter and see what the effect is.
If you permit yourself to send bare SQL statements
from the application, you face a much greater problem. You need to search a much
larger amount of code, and if the column has a common name like status
you are basically lost.
EXEC() and
sp_executesql
There are two ways to use dynamic SQL in MS SQL
Server.
EXEC()
EXEC() is the simplest, as illustrated by this
example:
SET @table = 'sales' + @year + @month
EXEC('SELECT * FROM ' + @table)While this looks
amazingly simple, there are a few important things to observe. The first is that
it is always the permissions of the current user that applies,
even if the statement appears in a stored procedure. The second is that EXEC()
is very similar to the EXEC of a stored procedure, but rather than calling a
stored procedure, you invoke batch of single SQL statements. This has a number
of implications:
- Within the SQL batch you cannot access local
variables or parameters of the calling stored procedure.
- Any USE statement will not affect the calling stored
procedure.
- Temp tables created in the SQL batch will not be
available to the calling procedure since they are dropped when the block exits 每
just like when you exit a stored procedure. The batch can however access tables
created in the calling procedure.
- The query plan for the batch is not part of the plan
for the calling procedure. Cachewise the query is just as good as an bare SQL
statement sent from the client.
- If the batch results in an condition that leads to
abortion of the batch, for instance rollback in a trigger, not only the EXEC()
batch is terminated, but also the calling procedure (and its caller and so
on).
But in difference to a regular stored procedure call,
you cannot use parameters, nor is there any return value to retrieve. The value
of @@error will be related to the last statement in the batch. Thus, if an error
occurs in EXEC(), but is followed by a successful command, @@error will be
0.
EXEC() was introduced in SQL 6.0.
Don't confuse EXEC(@sql) with EXEC @sp, the
latter executes a stored procedure of which the name is in @sp.
sp_executesql
sp_executesql was introduced in SQL7, and has the
advantage that it permits parameters to the dynamic SQL, both input and output
parameters. Here is an example with an output parameter:
declare @sql nvarchar(4000),
@col sysname,
@min varchar(20)
set @col = N'au_fname'
set @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col + N')) FROM authors'
exec sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT
select @min
Thus, you can much easier get a value from your
dynamic SQL statement into a local variable than with EXEC(). (You can still do
that with EXEC(), by means of INSERT EXEC(), but that's quite a kludge.)
Another thing to notice is that with sp_executesql
there are better odds for the query plan to be reused, because sp_executesql
builds a prepared query. But you should observe that it depends on what you
parameterize on. If you change table or column each time, there will not be any
plan reused.
These points that we mentioned for EXEC() applies to
sp_executesql as well:
- The permissions of the actual user applies.
- Any USE statement will not affect the calling stored
procedure.
- Temp tables created in the SQL batch will not be
available to the calling procedure.
- Termination of the batch executed by sp_executesql
terminates the calling procedure too.
- @@error reports the status of the last statement in
the dynamic SQL code.
According to Books Online the return value from
sp_executesql is either 0 for success or 1 for failure. However it appears that
the return value is the final value of @@error, at least in SQL2000.
For a full description of sp_executesql, please see
Books Online.
Which to use
If you use dynamic SQL regularly, sp_executesql is
your best bet, because the query plan may be reused, and you can use parameters.
Beside the obvious case that you are stuck on SQL6.5, there is one more
situation where you need EXEC() and that is when the dynamic SQL is too long to
fit an nvarchar(4000). To wit, you can say:
EXEC(@sql1 + @sql2)
When you call
sp_executesql you can only use one variable, as T-SQL does not permit
expressions as parameters in call to stored procedures. But if you find it
amusing, you can actually say: EXEC('EXEC sp_executesql ' @sql1 + @sql2)
Cursors and dynamic SQL
Not that cursors are something you should use very
often, but people often ask about using dynamic SQL with cursors, so I give an
example for the sake completeness. You cannot say DECLARE CURSOR
EXEC(), you have to put the entire DECLARE CURSOR statement in dynamic
SQL:
SET @sql = 'DECLARE my_cur CURSOR FOR SELECT col1, col2, col3 FROM ' + @table
EXEC (@sql)
Note that when you do this, you cannot use a local
cursor.
Dynamic SQL and stored
procedures
Let's go back and review the reasons we use stored
procedure and what happens when we use dynamic SQL.
1. Permissions
If you cannot give users direct access to the tables,
you cannot use dynamic SQL, it is as simple as that. In some
environments, you may assume that users can be given SELECT access. But unless
you know for a fact that permissions is not an issue, don't use dynamic SQL for
INSERT, UPDATE and DELETE statements.
Thus we see here a significant delimitation on when
we can use dynamic SQL. If you are using application roles, or you are using MTS
without direct access for the users to the database, you can probably
overlook this problem. Nevertheless, as we shall see in the section One more security problem you may still have security
issues to consider.
It is also worth pointing out that with temp tables
there are never any permission issues. We will see an example in the next
section on when this can be useful.
And there yet one more case when permissions are not
an issue: you are writing admin procedures that will be run by a user with
sysadmin privileges.
2. Caching query plans
As we've seen the query plan for dynamic SQL is not
always cached. Does that mean that dynamic SQL always is slower than static SQL
in a stored procedure? No, advanced and well-considered use of dynamic SQL can
actually help to improve performance. But, alas, many of the questions
posted to newsgroups, calls for stored procedures like this one:
CREATE PROCEDURE general_select @tblname nvarchar(127),
@key key_type AS -- key_type is char(3)
EXEC('SELECT col1, col2, col3
FROM ' + @tblname + '
WHERE keycol = "' + @key + '"')
This is a fairly meaningless stored procedure. The
permission system is bypassed, so the user needs to have SELECT permission on
the table, and the query plan will not be cached. If performance or permissions
are issues for you, write one stored procedure for each table. If neither are
issues to you, you could just as well consider abandoning writing stored
procedures altogether, if you think the code above is fine.
But when can you gain performance with dynamic SQL?
One case I've used EXEC() to speed up code was a stored procedure where I
created a temp table, and also created an index on the table. I first filled up
the temp table with some basic selection, and then I needed to perform some
updates. To make SQL Server to use those indexes, I put those updates in EXEC().
Since this was a temp table, the permissions were not an issue. (You may know
that SQL Server may recompile a procedure during execution to handle changes in
temp tables, but this was with SQL 6.5 which does not have this
feature.)
3. Minimizing network traffic
In the two previous sections we have seen that
dynamic SQL in a stored procedure is not any better than bare SQL statements
from the client. With network traffic it is a different matter. There is never
any network cost for dynamic SQL in a stored procedure. If we look at the
example procedure general_select in the previous section, neither is
there much to gain. The bare SQL code takes about as many bytes as the procedure
call.
But say that you have a complex query which joins six
tables with complex conditions, and one of the table is one of sales0101,
sales0102 etc depending on which period the user wants data about. This
is a bad table design, that we will look into more, but assume for the moment
that you are stuck with this. If you solve this with a stored procedure with
dynamic SQL, you only need to pass the period and don't have to pass the query
each time. If the query is only passed once an hour the gain is negligible. But
if the query is passed every fifth second and the network is so-so, you can win
a lot here.
Presuming, as always of course, that you can afford
to give users the necessary permissions to the involved tables.
4. Using output parameters
If you write a stored procedure only gain the benefit
of an output parameter, you do not in any way affect this by using dynamic SQL.
Then again, you can get OUTPUT parameters without writing your own stored
procedures: you can call sp_executesql directly from the
client.
5. Encapsulating logic
There is not much new to add to what we said in our
first round on stored procedures.
6. Keeping track of what is used
Dynamic SQL is contradictory to this aim. If you
start to pass table names or column names as parameters you are in the same
situation we when you send bare SQL code from the client. Any use of dynamic SQL
will hide a reference, so that it will not show up in sysdepends. Neither will
the reference reveal itself when you build the database without the referenced
object. However, at times this may be a cost you might be prepared to take. In
our shop we do use dynamic SQL, but only sparingly. In 2500 stored procedures,
there are maybe 20-30 with dynamic SQL. And if I find, when reviewing some piece
of code, that a table or object name is passed as parameter, the programmer will
have to go back and change that. (As he will need if INSERT, UPDATE or DELETE
permissions are needed for the code to work.)
Other problems with using
dynamic SQL
Let's face it: using dynamic SQL is kludgy. Let's
review the procedure generic_select again:
CREATE PROCEDURE general_select @tblname nvarchar(127),
@key key_type AS -- key_type is char(3)
EXEC('SELECT col1, col2, col3
FROM ' + @tblname + '
WHERE keycol = "' + @key + '"')
Already here, we can see that we have to be careful
to have spaces after FROM and WHERE, and we must not forget to have quotes
around @key. If we go wrong, and for instance say:
CREATE PROCEDURE general_select @tblname nvarchar(127),
@key key_type AS -- key_type is char(3)
EXEC('SELECT col1, col2, col3
FROM' + @tblname + '
WHERE keycol = "' + @key + '"')
The procedure will compile, but when you run it you
will be told that none of the columns col1, col2, col3 or keycol exists.
This is because the resulting SQL now actually is, assuming the parameters
foo and abc:.
SELECT col1, col2, col3 FROMfoo WHERE keycol = "abc"
You might expect a syntax error, but
FROMfoo is a column alias for col3 and the FROM clause is optional in
T-SQL.
The best way to prevent this is to rewrite the
procedure to use a variable:
CREATE PROCEDURE general_select @tblname nvarchar(127),
@key key_type AS -- key_type is char(3)
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT col1, col2, col3
FROM ' + @tblname + '
WHERE keycol = "' + @key + '"'
EXEC sp_executesql @sql
If the EXEC() generates an error message, we can
easily debug the procedure by inserting a SELECT or PRINT statement. Or use any
SQL debugger we have at hand, for instance the one that comes with the Query
Analyzer in SQL Server 2000.
While we were at it, we replaced EXEC() with
sp_executesql. Note that this procedure requires an nvarchar (Unicode string) as
parameter.
You have may noted that I use two different types of
quotes in the example. I use single quote to enclose the literals that are part
of the procedure general_select, and I use double quotes to enclose the string
literal that is part of the dynamic SQL code. This is very useful to handle
nested quoting. Unfortunately, though, it's not really kosher. In fact, if you
try general_select from the Query Analyzer in SQL 2000, it will compile,
but you may not be able execute it, even if you create a table with the columns
keycol, col1, col2 and col3. This is because QA by default sends the command
SET QUOTED_IDENTIFIER ON to SQL Server. With this setting double
quotes are used to delimit identifiers, not string literals, in compliance with
the ANSI standard. To be compliant we must use single quotes on both levels. To
include a string delimiter in T-SQL you double it, so this the ANSI-compliant
version of general_select:
CREATE PROCEDURE general_select @tblname nvarchar(127),
@key key_type AS -- key_type is char(3)
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT col1, col2, col3
FROM ' + @tblname + '
WHERE keycol = ''' + @key + ''''
EXEC sp_executesql @sql
As you can imagine, when the dynamic SQL increases in
complexity, this will become very difficult to read and maintain. So I would
suggest the following.
CREATE PROCEDURE general_select @tblname nvarchar(127),
@key key_type AS -- key_type is char(3)
DECLARE @sql nvarchar(4000)
SET @sql = 'SET QUOTED_IDENTIFIER OFF
SELECT col1, col2, col3
FROM ' + @tblname + '
WHERE keycol = "' + @key + '"'
EXEC sp_executesql @sql
By including SET QUOTED_IDENTIFIER OFF in the dynamic
SQL, you can use double quotes as delimiter within that batch. If you have
column or table names that needs quoting, you can use square brackets [] in
place of the double quote. Of course, it's all very un-ANSI, but so is the whole
concept of dynamic SQL anyway.
What is the outcome of all this? That constructing
SQL code dynamically is a task that increases the complexity of your code, which
makes it more expensive to develop and maintain. And it does not help that you
use a language which is fairly primitive in string handling. That is, this is
sort of manipulation is easier to program in Visual Basic, C++, Perl or whatever
traditional language you prefer.
One more security
problem
Consider the procedure general_select again.
Say that a malicious user calls the procedure with this value for the parameter
@tblname:
some_table WHERE keycol = "ABC"
DELETE orders
SELECT col1, col2, col3 FROM some_tbl
If the
user only has SELECT access to the tables 每 this all that general_select
requires 每 then his evil plans will be crossed. But if you have granted
him full access, because you have procedures with dynamic SQL for INSERT, UPDATE
or DELETE , then he will get away with his deed. Now, obviously, if the user
have direct access to the database he can run any SQL statement, and don't need
to run any stored procedures at all.
But consider the case where you have locked out the
user from direct SQL access by means of application roles or a middle-tier
server like MTS? Then he cannot call the stored procedure directly, but if the
stored procedure receives user input unfiltered, then there is a hole that can
be exploited. This may be particularly sensitive in web applications, where the
input parameter is actually part of a URL.
You may think that it takes not only skill, but also
some luck for the user to find such a hole. But remember that there are too many
hackers out there with too much time on their hands.
This is not an issue which is reason to stay away
from dynamic SQL. But you should be aware of it, so that you can prevent those
security holes from appearing.A good start is to never to only pass strings that
comes from within the application, and never pass raw URL data as parameters.
You can also take precautions in the stored procedure. For instance, if you do
not use spaces in table or column names , you can easily check for space in a
parameter that is supposed to hold the name of a table or a column. Or simply
check whether the supposed objects actually exist.
Common cases when to (not)
use dynamic SQL
When you read the various newsgroups on SQL Server
there is almost every day someone who asks a question which is answered with
"use dynamic SQL" with a quick example, but ever so often the person answering
forgets to tell about the implications on permissions and caching. And, true,
many of these questions taken by the word have no other answer than dynamic SQL.
But it might be that the person asking didn't ask about his real problem, and
there is indeed alternatives available.
So in this section I will explore some cases where
you could use dynamic SQL, but not necessarily should, and will point to
alternative ways to dynamic SQL. Several of these cases could warrant articles
on their own, and I will be somewhat brief in places.
select * from
@tablename
A common question is why the following does not
work:
CREATE PROCEDURE my_proc @tablename sysname AS
SELECT * FROM @tablename
As we have seen we can make this procedure work with
help of dynamic SQL, but we have also seen that that would be a completely
meaningless procedure. If this is your idea of SQL programming, don't bother
about using stored procedures at all.
I guess that people asking about this are new to SQL
Server, but have a programming experiences in other languages such as C++, VB
etc where parameterisation is a good thing. Parameterizing the table name to
bring down the amount of code and to increase maintainability is simply a
programmer virtue.
But it is just that when it comes to database
objects, the old truth does not hold. Tables and columns should be seen as
constant objects. Why? Because of query plans, and because we need to keep track
of where tables and columns are used.
So if you want to do the above (save the fact that
SELECT * should not be used in production code), to save some typing, you are on
the wrong path. It is much better to write ten or twenty stored procedures, even
if they are similar to each other.
If your SQL statements are complex, so that there
actually is a considerable gain in maintainability to only have them in one
place, despite different tables being used, there is actually one strategy you
could consider: use a pre-processor like the one in C/C++. You would still have
one set of procedures per table, but the code would be in single include
file.
select * from sales +
@yymm
This is a variation of the previous case. The
difference is that in the previous section I presumed that there is a finite set
of tables. But some people appear to have systems where tables are created
dynamically. E.g., a new table to hold sales data is created for each month. In
this case the suggestion to have one procedure per table is not really viable,
not even with a pre-processor.
So there is no way out, but to use dynamic SQL? No,
take a step back and look at this situation: it is the wrong solution from the
start. Having one table for each month may make sense in a system like Access or
with flat files, but it doesn't in SQL Server. That year and month are simply
the first part of the primary key in your one single sales table.
If you have a legacy system, the cost for remodelling
may be prohibitive, but if you are developing a new system, just forget all
about dynamically created tables. Your code to access and update these table
will be messy, as we saw above. And if you are
creating these tables frequently, for instance one table for each shopping-cart
in an e-commerce system, you may create a hot spot in the system tables that may
be detrimental to performance.
update tbl set @colname =
@value where keycol = @keyval
In this case people want to update a column which
they select at run time. The above is actually legal in T-SQL, but what happens
is simply that @colname is assigned the value in @value for each affected row in
the table.
In this case dynamic SQL would call for the user to
have UPDATE permissions on the table, something which is not to take lightly. So
there is all reason to avoid it. Here is a fairly simple workaround:
UPDATE tbl
SET col1 = CASE @colname WHEN 'col1' THEN @value ELSE col1 END,
col2 = CASE @colname WHEN 'col2' THEN @value ELSE col2 END,
...
If you don't know about the CASE expression,
please look it up in Books Online. It's a very powerful feature of SQL.
But again, one would wonder why people want to this.
Maybe it's because their tables look like this:
CREATE TABLE products (prodid prodid_type NOT NULL,
prodname name_type NOT NULL,
...
sales_1 money NULL,
sales_2 money NULL,
...
sales_12 money NULL,
PRIMARY KEY (prodid))
It could make more sense to move these
sales_n columns to a second table: CREATE TABLE product_sales (prodid prodid_type NOT NULL,
month tinyint NOT NULL,
sales money NOT NULL,
PRIMARY KEY (prodid, month))
select * from @dbname +
'..tbl'
In this case the table is in another database which
is somehow determined dynamically. The people who ask this have definitely
gotten one thing right: you should never hard-wire a database name in your
stored procedures. One day you will want to have two environments on the same
server, for instance one production environment and one test environment. That's
when your hard-wired database references will come back and bite you.
There is however an alternative to dynamic SQL here.
It assumes that you can confine the operation on the other database in a
procedure on its own. In such case you can do things like:
SET @sp = @dbname + '..sp_in_other_db'
EXEC @ret = @sp @par1, @par2...
Some people who ask about this wants to perform
something on every database in the server. I would assume that if you want to do
this, it for some admin utility, so I guess that permissions are not an issue,
and neither caching, so dynamic SQL is perfectly OK. Nevertheless there is
exists an kind of alternative, sp_MSforeachdb, demonstrated by this
example:
sp_MSforeachdb "SELECT '?', COUNT(*) FROM sysobjects"
As you might guess, sp_MSforeachdb uses dynamic SQL
internally, so it is no actual alternative in that sense, but you are saved the
control loop. While sp_MSforeachdb is popular, it is actually not documented in
Books Online, so find out all about it, you need to look at the source in the
master database. But remember that undocumented means unsupported, thus
Microsoft are free to do whatever they want with it.
select * from tbl where col in
(@list)
This is possibly the most popular question for which
Use dynamic SQL! is a common answer.But in fact, while dynamic SQL is a
possible solution, it is completely unnecessary.
If you are on SQL2000 you can write a user-defined
table-valued function which transforms @list into a one-column table that you
can use in SELECT, DELETE or UPDATE statements. If you are on SQL7 or SQL6.5 you
don't have functions available. But you can still write a stored procedure that
unpacks @list into a temp table.
select * from tbl where
@condition
If you are considering writing the
procedure
CREATE PROCEDURE search_sp @condition varchar(8000) AS
SELECT * FROM tbl WHERE @condition
Just forget it. If you are doing this, you have not
completed the transition of using stored procedure and is still assembling your
SQL code in the client. But this example lapses into
Dynamic search
conditions
A not too uncommon case is that the user can select
data from a broad set of parameters. This can be handled with static SQL in the
following way:
CREATE PROCEDURE search_sp @par1 some_type1 = NULL,
@par2 some_type2 = NULL,
...
@parn some_typen = NULL WITH RECOMPILE AS
SELECT Col1, Col2, ...
FROM tbl
WHERE (Col1 = @par1 OR @par1 IS NULL)
AND (Col2 = @par2 OR @par2 IS NULL)
...
AND (Coln = @parn OR @parn IS NULL)
Here is the assumption that if a parameter is
NULL, then it is not to be included in the search. Say that Col1 is always
included in the search and NULL in @par1 means that we are looking for NULL in
Col1. That clause then needs to be (Col1 = @par1 OR Col1 IS NULL AND @par1 IS NULL)
The problem with this approach is that it may result
in bad performance, because the optimizer gets feed a lot of extra conditions it
may not be smart enough to ignore. Note that there is a WITH RECOMPILE in the
procedure definition. This is almost necessary, because two successive calls may
require completely different indexes to be used.
So actually, this is a case where dynamic SQL may
help to improve performance. By building an SQL statement with only the relevant
clauses, the optimizer has better chances to find a good plan. But of course, if
you cannot give SELECT permissions to your users, you still cannot use dynamic
SQL.
select * from table order by
@col
This can easily be handled without dynamic SQL in
this way:
SELECT col1, col2, col3
FROM tbl
ORDER BY CASE @col1
WHEN 'col1' THEN col1
WHEN 'col2' THEN col2
WHEN 'col3' THEN col3
END
Again, review the CASE expression in Books Online, if
you are not acquainted with it.
SQL Server
MVP Itzik Ben-Gan has a good article on this topic in March 2001 issue of SQL Server Magazine, where he offers
other suggestions.
select top @n from table order by
@col
Here is a simple way to achieve this:
CREATE PROCEDURE get_first_n @var int WITH RECOMPILE AS
SET ROWCOUNT @var
SELECT *
FROM authors
ORDER BY au_id
SET ROWCOUNT 0
You may have learnt that the SQL optimizer does
not care about SET ROWCOUNT, and this is true in SQL6.5 (where you don't have
TOP, so you have no other choice anyway). But in SQL7 and SQL2000 it does.
However, you need to be careful to actually use a parameter (and not a local
variable) as the argument to SET ROWCOUNT, or else the optimizer will not know
the value and may pick a bad plan.
Also, be careful to say SET ROWCOUNT 0 after the
SELECT statement, because otherwise other statements in the procedure will be
affected.
Books Online has some caveats on SET ROWCOUNT, and
discourages use of SET ROWCOUNT with DELETE, INSERT and UPDATE statements.
Exactly why, I don't know, but I would suggest that INSERT into a temp table is
OK. INSERT into a table with triggers can cause surprises, because the ROWCOUNT
restriction applies to the trigger as well.
It may also be worth considering why you do this. If
your intention is to limit output to a web page, it may be a better strategy to
read big chunks of 500 rows at a time, so you don't have to make the round-trip
to the database, next time the user clicks "Next". (And personally, I have
always been irritated of web sites that limits output to 10-20 items at a
time.)
create table @tbl
In this case there is no issue on neither permissions
nor caching. And, for that matter, not on dependencies either. (It's not an
issue about permissions, because the user will need to have permissions to a
create a table even if it's a static command in a procedure.) So from that point
of view, there is no argument against using dynamic SQL.
But still one question remains: Why? Why would you
want to this? It might make sense in some admin script where you actually
need to create a couple of tables similar to each other. But if you are creating
tables on the fly in your application, I don't think you are using SQL Server in
a very good way. See also the discussion under select *
from sales + @yymm.
Sometimes when people are doing this, it appears that
they want to construct unique names for temporary tables. This is completely
unnecessary, as this is a built-in feature in SQL Server. If you say:
CREATE TABLE #nisse (a int NOT NULL)
Then the actual names behind the scenes will be
something much longer, and no other connections will be able to see this
instance of #nisse.
If you want to create a permanent table which is
unique to a client, because you are using disconnected record sets and cannot
use temp tables, it may be better to create one table which all clients can
share, but where the first column is a key which is private to the
client.
Dynamic column
widths
Here is a quite different problem. If you write a
stored procedure to be used in an application, there is no reason to bother
about column widths. But if you write an procedure to be run by DBA or an
operator from the Query Analyzer, you may want to optimize output, so that it
doesn't include lots of empty space.
The undocumented, but still very popular, procedure
sp_who2 is a prime example of this. It gathers all data into a temp table. Then
it determines the widest value for each column, and then builds an SQL statement
that converts all columns to varchars big to fit the widest value but not more.
I encourage you to look at the source code in the master database for this
procedure to see the technique in play.
Acknowledgements and
Feedback
These people has provided valuable input for this
article (including the sub-article on list functions): Tibor Karaszi, Keith
Kratochvil, Pankul Verma, Bharathi Veeramac (whose list-to-strings
function got me going) and Steve Kass.
If you have suggestions for improvements or
corrections on contents, language or formatting, please mail me at sommar@algonet.se. If you have technical
questions that any knowledgeable person could answer, I encourage you to post to
any of the newsgroups microsoft.public.sqlserver.programming
or comp.databases.ms-sqlserver.
