Dynamic SQL: EXEC()
Dynamic SQL can be very powerful skill to have in your tool kit. It allows the developer to write one piece of code and re-use it for different reasons. All you need to do is pass in the variables that change from script to script and you can build out the script you want to execute and then execute once. Below I will walk through a basic example and a more complicated example. Each example will execute the code with the command “EXEC(@SQL)”.
Basic Dynamic SQL
I have two examples here, with 2 different results. The difference between these 2 scripts are just the TableName and the SelectStatement. The @SQL command (which executes the code) is the same. You can see that the same script, passing in different variables, has 2 different results.
DECLARE @SelectStatement AS NVARCHAR(255)
DECLARE @SQL AS NVARCHAR(MAX)
SET @TableName = 'SALES.SalesOrderHeader'
SET @SelectStatement = 'TOP 10 SalesOrderNumber, PurchaseOrderNumber, AccountNumber '
SET @SQL = 'SELECT ' + @SelectStatement + CHAR(13) +
'FROM ' + @TableName + CHAR(13)
PRINT @SQL
EXEC(@SQL)
DECLARE @SelectStatement AS NVARCHAR(255)
DECLARE @SQL AS NVARCHAR(MAX)
SET @TableName = 'SALES.SalesTerritory'
SET @SelectStatement = 'TerritoryID, [Name],CountryRegionCode, [Group],SalesYTD '
SET @SQL = 'SELECT ' + @SelectStatement + CHAR(13) +
'FROM ' + @TableName + CHAR(13)
PRINT @SQL
EXEC(@SQL)
Complex Dynamic SQL
Is this more complex example, I am building upon our cursor example, which you can find here. For this example, I’m passing in the Select Statement, TableName, Join, Group by, Order by (if there are any). This script will join the two tables from above and return a sum of the SubTotal by Territory. As the cursor example, I will walk through the example.
DECLARE @TableName AS NVARCHAR(255)
DECLARE @ON_Join AS NVARCHAR(255)
DECLARE @Join_Type AS NVARCHAR(255)
DECLARE @SelectStatement AS NVARCHAR(255)
DECLARE @GroupBy AS NVARCHAR(255)
DECLARE @OrderBy AS NVARCHAR(255)
DECLARE @SQL_SELECT AS NVARCHAR(255)
DECLARE @SQL_FROM AS NVARCHAR(255)
DECLARE @SQL_GroupBY AS NVARCHAR(255)
DECLARE @SQL_OrderBY AS NVARCHAR(255)
DECLARE @Counter AS INT
DECLARE @ORD AS INT
DECLARE @SQL AS NVARCHAR(MAX)
SET @Counter = 0
SET @SQL = ''
DECLARE CURSOR_NAME CURSOR FOR
SELECT 1 AS ORD, 'SALES.SalesOrderHeader SOH' AS TableName, '' AS ON_Join, '' AS Join_Type, 'ROUND(SUM(SOH.SubTotal),0) AS Sales_Revenue' AS SelectStatement, '' AS GroupBy, 'ROUND(SUM(SubTotal),0) DESC' AS OrderBy
UNION SELECT 2 AS ORD, 'SALES.SalesTerritory ST' AS TableName, 'SOH.TerritoryID = ST.TerritoryID' AS ON_Join, 'INNER JOIN' AS Join_Type, 'ST.[NAME] AS TerritoryName' AS SelectStatement, 'ST.[NAME]' AS GroupBy, '' AS OrderBy
OPEN CURSOR_NAME
FETCH NEXT FROM CURSOR_NAME INTO @ORD, @TableName, @ON_Join, @Join_Type, @SelectStatement, @GroupBy, @OrderBy
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ORD = 1
BEGIN
SET @SQL_SELECT = @SelectStatement
SET @SQL_FROM = @TableName
SET @SQL_GroupBY = @GroupBy
SET @SQL_OrderBY = @OrderBy
END
ELSE
BEGIN
SET @SQL_SELECT = @SQL_SELECT + ', ' + @SelectStatement
SET @SQL_FROM = @SQL_FROM + ' ' + @Join_Type + ' ' + @TableName + ' ON ' + @ON_Join
SET @SQL_GroupBY = @SQL_GroupBY + @GroupBy
SET @SQL_OrderBY = @SQL_OrderBY + @OrderBy
END
PRINT '--------------------- START -----------------'
PRINT '@Counter: ' + CONVERT(VARCHAR(10), @Counter)
PRINT '@TableName: ' + @TableName
PRINT '@ON_Join: ' + @ON_Join
PRINT '@Join_Type: ' + @Join_Type
PRINT '@SelectStatement: ' + @SelectStatement
PRINT '@GroupBy: ' + @GroupBy
PRINT '@OrderBy: ' + @OrderBy
PRINT '@SQL_SELECT: ' + @SQL_SELECT
PRINT '@SQL_FROM: ' + @SQL_FROM
PRINT '@SQL_GroupBY: ' + @SQL_GroupBY
PRINT '@SQL_OrderBY: ' + @SQL_OrderBY
PRINT '--------------------- END -----------------' + CHAR(13)
SET @Counter = @Counter + 1
END
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME
SET @SQL = 'SELECT ' + @SQL_SELECT + CHAR(13) +
'FROM ' + @SQL_FROM + CHAR(13) +
CASE WHEN @SQL_GroupBY = '' THEN '' ELSE 'GROUP BY ' + @SQL_GroupBY END + CHAR(13) +
CASE WHEN @SQL_OrderBY = '' THEN '' ELSE 'ORDER BY ' + @SQL_OrderBY END
PRINT '--------------------- SQL QUERY -----------------'
PRINT @SQL
EXEC (@SQL)
SQL Scripts
Lets walk through the script step by step.
1) As most scripts, we first Declare the variables that would need to be used in the script.
@TableName: This will be the table name to be used in the final script. Whether it’s the first table or a table that we would join to.
@ON_Join: These are the columns to join the 2 tables.
@Join_Type: This will be what type of join that will be used. If it is INNER, LEFT, RIGHT, etc.
@SelectStatement: This will store the columns from the table in the same row that should be in used in the select statement.
@GroupBy: The columns in the script that should be Grouped By.
@OrderBy: The columns in the script that should be Ordered By.
@SQL_SELECT: This is the the overall Select statement. Within each Loop, the Select script builds upon the previous loop entry. We will look into this in more detail in a bit.
@SQL_FROM: This takes the table names from each loop and builds out the From statement.
@SQL_GroupBY: Same as above, each GroupBy entry is built out to have the whole Group By statement.
@SQL_OrderBY:
@Counter: Same as in our basic cursor script, counter here is only being used to identify where in the loop the script is in running.
@ORD: The first row in the script is identified as 1 and therefore used later in the loop to identify
2) SET NOCOUNT ON: This line will remove in the message console showing how many records were returned. For example, when a select statement of 10 records is executed, in the message console you would normally see “(10 row(s) affected)”. I typically don’t like to see this when I want to see what I have written to print out. In those cases I set this to “ON” to remove this message.
DECLARE @TableName AS NVARCHAR(255)
DECLARE @ON_Join AS NVARCHAR(255)
DECLARE @Join_Type AS NVARCHAR(255)
DECLARE @SelectStatement AS NVARCHAR(255)
DECLARE @GroupBy AS NVARCHAR(255)
DECLARE @OrderBy AS NVARCHAR(255)
DECLARE @SQL_SELECT AS NVARCHAR(255)
DECLARE @SQL_FROM AS NVARCHAR(255)
DECLARE @SQL_GroupBY AS NVARCHAR(255)
DECLARE @SQL_OrderBY AS NVARCHAR(255)
DECLARE @Counter AS INT
DECLARE @ORD AS INT
DECLARE @SQL AS NVARCHAR(MAX)
SET @Counter = 0
SET @SQL = ''
2. Next section, I’m declaring the cursor to be used and selecting the data. In the select, I hard-coded the result but if I use this in real world, I store these values in SQL tables. The results of the select statement are below as well.
SELECT 1 AS ORD, 'SALES.SalesOrderHeader SOH' AS TableName, '' AS ON_Join, '' AS Join_Type, 'ROUND(SUM(SOH.SubTotal),0) AS Sales_Revenue' AS SelectStatement, '' AS GroupBy, 'ROUND(SUM(SubTotal),0) DESC' AS OrderBy
UNION SELECT 2 AS ORD, 'SALES.SalesTerritory ST' AS TableName, 'SOH.TerritoryID = ST.TerritoryID' AS ON_Join, 'INNER JOIN' AS Join_Type, 'ST.[NAME] AS TerritoryName' AS SelectStatement, 'ST.[NAME]' AS GroupBy, '' AS OrderBy
OPEN CURSOR_NAME
FETCH NEXT FROM CURSOR_NAME INTO @ORD, @TableName, @ON_Join, @Join_Type, @SelectStatement, @GroupBy, @OrderBy
WHILE @@FETCH_STATUS = 0
BEGIN
3. Within the cursor, we start to build out the SQL statement that will get executed. What I am doing within the cursor is building out the eventual SQL statement.
a.) ORD = 1: If I am dealing with the first table in our script, I want to start the script that we are building out. If ORD <> 1 then the script will take what the original values were and add the new values from the loop. For example, in our example above, SQL_Select = “ROUND(SUM(SOH.SubTotal),0) AS SalesRevenue” if ORD =1. However, of ORD <> = 1, SQLSelect = “ROUND(SUM(SOH.SubTotal),0) AS Sales_Revenue” + “,” + “ST.[NAME] AS TerritoryName”. This will continue for the select, from, groupby, and orderby variables. And this will continue for every table that is listed. In this case, there are only 2 tables.
b.) PRINT- The cursor will also print out each variable to the message console. This is a good way to see how a script is built out. Below I have an image of the results and highlighted matches the example in 3a.
IF @ORD = 1
BEGIN
SET @SQL_SELECT = @SelectStatement
SET @SQL_FROM = @TableName
SET @SQL_GroupBY = @GroupBy
SET @SQL_OrderBY = @OrderBy
END
ELSE
BEGIN
SET @SQL_SELECT = @SQL_SELECT + ', ' + @SelectStatement
SET @SQL_FROM = @SQL_FROM + ' ' + @Join_Type + ' ' + @TableName + ' ON ' + @ON_Join
SET @SQL_GroupBY = @SQL_GroupBY + @GroupBy
SET @SQL_OrderBY = @SQL_OrderBY + @OrderBy
END
PRINT '--------------------- START -----------------'
PRINT '@Counter: ' + CONVERT(VARCHAR(10), @Counter)
PRINT '@TableName: ' + @TableName
PRINT '@ON_Join: ' + @ON_Join
PRINT '@Join_Type: ' + @Join_Type
PRINT '@SelectStatement: ' + @SelectStatement
PRINT '@GroupBy: ' + @GroupBy
PRINT '@OrderBy: ' + @OrderBy
PRINT '@SQL_SELECT: ' + @SQL_SELECT
PRINT '@SQL_FROM: ' + @SQL_FROM
PRINT '@SQL_GroupBY: ' + @SQL_GroupBY
PRINT '@SQL_OrderBY: ' + @SQL_OrderBY
PRINT '--------------------- END -----------------' + CHAR(13)
SET @Counter = @Counter + 1
4. In this section, I close the cursor, build the final SQL statement (@SQL), and print and execute that script. Building out the script here is very similar to what is done in the basic dynamic example. The difference is that the cursor is used to build the Select, From, Group by, and Order by variables. The very last line “EXEC (@SQL)” is what will execute the script. Below is the printed variable to the console and the file results from the EXEC command.
END
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME
SET @SQL = 'SELECT ' + @SQL_SELECT + CHAR(13) +
'FROM ' + @SQL_FROM + CHAR(13) +
CASE WHEN @SQL_GroupBY = '' THEN '' ELSE 'GROUP BY ' + @SQL_GroupBY END + CHAR(13) +
CASE WHEN @SQL_OrderBY = '' THEN '' ELSE 'ORDER BY ' + @SQL_OrderBY END
PRINT '--------------------- SQL QUERY -----------------'
PRINT @SQL
EXEC (@SQL)