Create and Execute Dynamic SQL on T-SQL

Have you ever had the need to use dynamic SQL? Chances are that you had the need to manipulate a string to conform a SQL statement and then execute it via a SQLCommand -> CommandType.Text. This is a common technique in the .NET world, but how about using dynamic SQL on a Stored Procedure?

At times, I had the need to bring data from a table column and then use it to conform a SQL statement that will then be executed on demand. For example:

Imagine you have a table named t_settings, with a field named target_db. In this field, we store a database name. Now, using the data on this field, you could do something like:

declare @sql varchar(255)
select @sql = 'select * from ' + rtrim(target_db) + 
              '..another_table where 1=1' from t_settings
execute (@sql)

The execute statement provides all the magic. It allows you to execute a string command just as you would do with a SP.

Below is a somewhat silly example that you can try if you have the AdventureWorks database available.

declare @ContactID varchar(3)
declare @Sql varchar(255)
select @ContactID = Cast(ContactID as varchar) from 
        person.contact where EmailAddress = 'kim3@adventure-works.com'
select @ContactID
--Manipulate the String that will become the query that we need to execute.
select @Sql = 'Select * from Sales.SalesOrderHeader where contactid = ' + @ContactID
execute (@sql)

Happy coding, Will.