Dynamic SQL

如果你经常关注SQL Server的NewsGroups，你会发现人们经常会问为什么我不能运行这样的SQL语句： 
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?
• EXEC() and sp_executesql
• Dynamic SQL and stored procedures
• Other problems with using dynamic SQL
• One more security problem
• Common cases when to (not) use dynamic SQL
  • select * from @tablename
  • select * from sales + @yymm
  • update tbl set @colname = @value where keycol = @keyval
  • select * from @dbname + '..tbl'
  • select * from tbl where col in (@list)
  • select * from tbl where @condition
  • Dynamic search conditions
  • select * from table order by @col
  • select top @n from table order by @col
  • create table @tbl
  • Dynamic column widths
• Acknowledgements and feedback

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.