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.
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!