So what is Dynamic SQL

With Dynamic SQL you cannot predict the complete SQL Statement in advance. So you need to construct the Query to be executed based on one or more Parameters that are supplied.

To do so, you would need to construct the SQL Statement first as a text. But a single string of text cannot be executed straight away. Luckily SQL Server has a build-in Stored Procedure that you can use.

Let's look at a simple example first.

Passing the string to be executed

In this example I declare a variable @sql and put a simple SQL Statement in it. Then I pass the variable to the build-in Stored Procedure: sp_ExecuteSQL. This Stored Procedure will execute the Query as if it was run directly.

declare @sql nvarchar(1000)
set @sql = 'select count(*) from sys.tables'
exec sp_ExecuteSQL @sql

Using an incoming Variable

In the previous example, the SQL Statement itself is Static, as it does not change. The next example is how it changes depending on a Variable.

declare @test nvarchar(100) = 'test'
declare @sql nvarchar(1000)
set @sql = concat('select ''', @test, '''')
exec sp_ExecuteSQL @sql

Now there is a problem however here, though it might not be that obvious at first glance.

If you have complete control over the incoming Value of the Variable, there might not be a problem. But if the Value comes from some User Input Field, you could be in trouble. The User could try and do some SQL Injection:

declare @test nvarchar(100) = 'Test'' GO select 123, '''
declare @sql nvarchar(1000)
set @sql = concat('select ''', @test, '''')
exec sp_ExecuteSQL @sql

With this one you will see that 2 Queries are executed. The Query that you expected, of course. But right after that, the SQL Statement I put after the GO command is also executed.

In this case, no worries because it is just another simple Select Statement. But can you imagine if someone would put some Truncate or Drop Table Statement there? That is why with Dynamic SQL you should never use a concatinated string.

Instead you should use the optional Parameters from sp_ExecuteSQL:

declare @test nvarchar(100) = 'test'
--set @test nvarchar(100) = 'Test'' GO select 123, '''
declare @sql nvarchar(1000) = 'select @this'
exec sp_ExecuteSQL @sql, N'@this nvarchar(100)', @this = @test

As you can see, sp_ExecuteSQL now has 2 Parameters. The first one is used the let sp_ExecuteSQL now that there is a Variable in the SQL Statement to be executed. But this is a SQL Statement inside another SQL Statement. And the inside Statement does not now anything from the outside Statement.

Therefor you need to tell the inside Statement that the Value to be used inside (@this) is that what's in the outside Variable (@test).

Now if you would uncomment the second line and run the Query again, you will see that only one Statement is executed.

Now if the incoming Variable cannot be manipulated from outside, a concatinated string would not be that dangerous. But still, it is better to be safe and train yourself to never use optional Parameters and let sp_ExecuteSQL handle it. It is not that hard and your code will also be ready for future changes. Maybe the Variable cannot be manipulated from the outside today/ But can you predict if that will always stay like that in the future?

Using the result from Inside

So now you know how you can execute a SQL Query inside another one. Next step is to get the result from the Inside Query so it can be used in the outside Query.

For this example let's loop through all the databases on the server and do a count on the number of tables. Here we will encounter some new problems that we will need to solve. First one is switching from one Database to another with the Use Statement. Try these:

declare @sql nvarchar(1000)
set @sql = 'select @db'
exec sp_ExecuteSQL @sql,
N'@db nvarchar(100)',
@db = 'Cloudbusters'
GO

declare @sql nvarchar(1000)
set @sql = 'use @db select 1'
exec sp_ExecuteSQL @sql,
N'@db nvarchar(100)',
@db = 'Cloudbusters'
GO

The first one will work, the second one will fail. That is because the Database Name is a special DataType in SQL Server. Now you might get it to work by concatenating string, but hey, I told you not to use that with Dynamic SQL. So how do we solve this one?

Well, sp_ExecuteSQL is a Stored Procedure. So we should not change the Database in the Query. Instead, we should use the sp_ExecuteSQL Procedure from the right Database. And we can do that by its Fully Qualified Name. Not just sp_ExecuteSQL but [database].sys.sp_ExecuteSQL.

declare @db sysname = N'master'

declare @switch nvarchar(max) = quotename(@db) + N'.sys.sp_executesql',
@sql nvarchar(max) = N'select db_name()';

exec @switch @sql

Why the Quotename you might ask? Well, you might have a Database with a name that is numerical. And maybe you already know that with the Use Statement this will not work, unless you put Square Brackets around it. Quotename does the trick for you.

By the way, did you notice I used Dynamic SQL to execute Dynamic SQL here?

Now the get the Result Value from the Inside Query, you need to store it in a Variable. That could be done with something like 'select @cnt = count(*) from sys.tables'. Like we saw before, when calling sp_ExecuteSQL that has a Variable inside, you must specify the extra Parameters to let it now which Parameter(s) are used and what the Values are.

Now we don't want to send anything to the Inside Query, we want to get something out of it. By default, a Parameter goes to the inside. Here we want the oposite so we use the keyword Output to make it a Parameter that can be used for the output. So that would look like this: N'@cnt int output'

Same for the Value. We don't want to set the Value, we want to get it out of the Inside Query. Again we use the Output keyword for that: @cnt = @count output.

But why wouldn't we just use the @cnt Variable itself further down? That is because the @cnt Variable only exists while sp_ExecuteSQL is executing. When it is done, @cnt no longer exists.

So here is the complete Query:

declare @DB nvarchar(100)

declare cDatabases cursor for
select Name
from sys.databases
order by Name

open cDatabases
fetch next from cDatabases into @DB
while @@FETCH_STATUS = 0
begin
  declare @switch sysname = quotename(@db) + N'.sys.sp_executesql'
  declare @sql nvarchar(1000)
  declare @count int

  set @sql = 'select @cnt = count(*) from sys.tables'

  exec @switch @sql,
    N'@cnt int output',
    @cnt = @count output

  print concat(
    cast(@count as nvarchar(10)),
    ' table(s) in DB ',
    @DB
  )
  print ''

  fetch next from cDatabases into @DB
end

close cDatabases
deallocate cDatabases

That's it for today

I once was hired to run a simple query to do a Database repair. According to the Project Manager it had to be executed in a few Databases. What he failed to tell was that it had to be executed on over 2000 Databases. That's just a little more than a few.

He expected that this would take at least a day or two. Can you imagine his suprprise when I was done in less than 10 minutes?

Ah, you just got to love some Dynamic SQL!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!