SQL Server

Since two of the scripts I’ve posted so far on Mondays involve the use of dynamic SQL code, I figured it would be a worthwhile to post on using dynamic SQL in SQL Server. After all, understanding and using dynamic SQL is one of the fundamental tools for a DBA to make management tasks automated, helping you save time and eliminate “fat fingers”.

What is dynamic SQL?

Before we get started, let’s define what we’re talking about. A quick Googling gives us a bunch of links that all describe the same process: “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”. What this means to us is now we can take SQL code that we want to run repeatedly with some changing parts, and write it in a way where those changes can be handled by other logic and keep the rest of the query unchanged, saving us a whole lot of time and effort.

Parametrization and code reuse

We’ll use my sp_spaceused script to show dynamic SQL in action. First off, we should review what the objective of that script is: running sp_spaceused to give us information about the size of the tables in our database in context with the other tables. Since the stored procedure only gives us information one table at a time, we need a way to gather all that information without having to run each query separately. We know that because the stored procedure accepts the table name as a parameter, the core code will remain the same no matter what we pass to it, so we only need to handle the changing table name.

Let’s look at the code:

declare recscan cursor for
select name from sysobjects where type = 'U'

open recscan
fetch next from recscan into @curr
while @@fetch_status = 0

begin
insert into @return
exec sp_spaceused @curr

fetch next from recscan into @curr
end

In the first two lines, we have a SQL cursor. If you need more information on cursors and how they work, hit up MSDN, but the short version is they give us a way to create a data set that we can use programmatically for processing. In this specific case, I’m building a data set of all the user tables in my database.

The next part is to open our list of table names and grab the first item in it. I’ll take that table name (the next available value in my cursor) and stuff it into a variable. Now I can use the stored procedure, with my variable as a parameter, to execute the code and get my return set. The rest of a the code is a simple loop that will continue until the SQL engine can no longer fetch a value from the cursor.

At the end of the day, we have two pieces that make this work:

• A static set of code that performs the same task, but with a parametrized value that can alter the target of that task.
• A programatically changing data set that we can use to provide the parameter for our code statement, so we can apply the same work to different targets.

But is this really dynamic SQL, since all we’re doing is using a stored procedure driven by a changing parameter? Sure thing, because while the stored procedure code remains the same, we are changing the parameter programmatically. We aren’t limited to just using stored procedures, though. While there are a lot of advantages to using stored procedures, such as pre-compiled SQL statements and making use of the procedure cache, sometimes you need something more fine grained. Fortunately, SQL server provides us with a couple other methods to execute dynamic SQL.

EXECution time

We can also use the EXECUTE function for more than just calling a stored procedure. It will also accept a SQL statement passed to it as a string. Let’s see how this works by modifying our script to get the number of tables in each database instead of the space used:

declare @sql varchar(max)
declare @db varchar(100)
declare @return table
(db_name varchar(100),
table_count int)

declare recscan cursor for
select name from sysdatabases where dbid >4

open recscan
fetch next from recscan into @db
while @@fetch_status = 0
begin
set @sql = 'select '''+@db+''',count(*) from '+@db+'.sys.objects where type = ''U'''

insert into @return
exec(@sql)
end

close recscan
deallocate recscan

select * from @return

Now we can take our SQL statement and change the database context programmatically, allowing us to reuse the same query over and over again.

SQL Server also provides the sp_executesql stored procedure as an alternative to using EXECUTE with two major differences. The first is that sp_executesql allows you to use parameters within your dynamic statement, so it allows you a cleaner option for parametrization. The second is you can use it to capture an output parameter, giving you some other options for using the data, though you are limited in using the statement output within an insert.

Finally, we can also make use of two undocumented stored procedures in SQL server, sp_MSforeachtable and sp_MSforeachdb. Just like they’re names imply, you can use these stored procedures to execute a SQL statement against every table in a database or every database on a server. While they don’t offer you as much control over your execution, they are pretty handy.

I’ve found dynamic SQL to be a huge time saver, since it is so flexible and powerful. With it, I can build out scripts to perform any number of tasks that remain the same, giving me the security in knowing that the task will run the same way, no matter how my parameters change.  Keep in mind that, while we only used SELECTs for the examples, you can use any SQL statement you normally would write ad hoc. For additional information and examples, I highly recommend Introduction to Dynamic SQL on SQLTeam.com, which will provide you more detailed information on the use of the different methods, as well as permissions and scope.