I’m pretty sure this is the first time I’ve brought up dynamically generated SQL statements. But it’s time you learn about them. There are times where you’ll need to run the same statement against multiple tables. Or agains multiple databases. Sometimes, you may not know how many columns you’ll need to create or select during run time. There are many reasons you could use dynamically generated statements, so today, that’s what we’re coving.

sp_executeSQL

The basis of your dynamically generated statement will be the command sp_executeSQL. It lets you run a string as a SQL statement. All the same rules for T-SQL apply here. Let’s look at a simple case:

USE adventureWorks
GO
DECLARE @sql VARCHAR(1000)
SET @sql = 'SELECT TOP 10 * FROM HumanResources.Employee'
--now run @sql
EXEC sp_execureSQL @sql
--you will get the same results by running
SELECT TOP 10 * FROM HumanResources.Employee

You could shortcust the EXEC statement by simply calling

USE adventureWorks
GO
DECLARE @sql VARCHAR(1000)
SET @sql = 'SELECT TOP 10 * FROM HumanResources.Employee'
EXEC(@sql)

But there is a reason I want you to learn to use sp_executeSQL. What if you wanted to use variables? With EXEC(@sql), you’d have to build some nasty SQL statements. What do I mean by nasty? Well, you’d have to read the variables into the string, that means using CAST or CONVERT to turn the variable datatype into a VARCHAR. That means, you’d have to start using multiple single quotes, and keeping track of how many you’ve used…

Trust me, you don’t want to have to do it that way. Debugging can be a beast.

Instead of that, you can actually include a parameter list as the second variable to the sp_execute SQL command. Check this example out.

USE adventureWorks
GO
DECLARE @IntVariable int
DECLARE @SQLString nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)
/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM HumanResources.Employee
       WHERE BusinessEntityID = @BusinessEntityID'
/* build the parameter list one time. */
SET @ParmDefinition = N'@BusinessEntityID tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 197
EXEC sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable
/* again, this would be the same as running: */
SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM HumanResources.Employee
       WHERE BusinessEntityID = 197

In this example, we built a query that accepts one parameter, Business Entity ID. So in order to dynamically call this query, and pass the parameter, we have to define it as a valid parameter. Since the Business Entity is a tiny int, we define that in our second SET statement. Finally, we declare the value in the third SET statement, then EXEC the command.

This is your first step from simple SQL to something more advanced. Once you understand this concept, you can do some really cool things. Want to know how I check for indexes automatically? How about how I check for missing foreign keys? All that and more can be done with dynamic SQL statements.

If you have any questions, please let me know. I’m here to help!