Dynamic SQL Redux - 11 Nov 1999

In a previous UPDATE, I wrote about using stored procedures with SQL Server. That topic really unleashed some email to me. Last week, I tried to clarify my comments about stored procedures. Now I’ll take another step forward and look at other comments that readers sent.

Last week, I wrote that my definition of a dynamic SQL statement is a standard SQL statement such as SELECT * FROM authors. Let’s explore this definition. You can execute many types of SQL by using Query Analyzer or other tools. A couple of readers pointed out that this type of syntax works:

declare @sql   varchar(50)select @sql =  'sp_who'execute @sql   'sa'

You can enter this code in Query Analyzer and execute it. The code executes the sp_who stored procedure by placing the stored procedure’s name in the SQL variable, then passing the variable to the execute command. This technique can be handy when you need to pass in the name of a stored procedure to another stored procedure or when you otherwise manipulate stored-procedure names within your code. For instance, if you’re working with a group of stored procedures whose only difference is the last part of their names, you can build the complete stored-procedure name in your code, then pass it to the execute command, as the code above does. This is certainly dynamic code because the server evaluates the SQL and because you can dynamically manipulate the code.

Also, in response to my examples in last week’s UPDATE, Kalen Delaney pointed out that instead of the statement I proposed last week

 SELECT * FROM authors; SELECT * FROM titles;

you can use this statement without the semicolon:

 SELECT * FROM authors  SELECT * FROM titles;

In this case, the space works as a delimiter. However, I don’t like the syntax. The statement is confusing because it uses spaces for delimiters between the SQL keywords in each statement and between the statements. Using a semicolon between the two statements, as in the first example, makes the statement easier for me to read.

It’s interesting to get reader feedback on SQL Server and related technologies. The world today is so complex that no one can know everything, and if we share our knowledge, we all improve over time.