How to 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 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

CodeProject