Build Dynamic SQL in a Stored Procedure

After reading this article, you will understand the basics of dynamic SQL; how to build statements based on variable values, and how to execute those constructed statements using sp_executesql and EXECUTE() from within a stored procedure.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases, AdventureWorks and WideWorldImporters. You can get started using these free tools with my Guide Getting Started Using SQL Server.

Build Dynamic SQL in a Stored Procedure

Most SQL we write is written directly into the stored procedure. It is what is called static SQL. It is called this because it doesn’t change. Once it is written, its meaning is set, it’s hammered into stone.

Below is an example of static SQL:

SELECT    JobTitle, Count(BusinessEntityID)
FROM      HumanResources.Employee
WHERE     Year(BirthDate) = 1970
GROUP BY  JobTitle

SELECT    JobTitle, Count(BusinessEntityID)
FROM      HumanResources.Employee
WHERE     Year(BirthDate) = 1971
GROUP BY JobTitle

Notice there are two statements, each returning a summary of JobTitles for a specific employee birth year. If we want to add more birth years, we need to add more statements. What if we only had to write the statement once and be able to change the year on-the-fly?

This is where dynamic SQL comes into play.

Dynamic SQL is SQL that is created and executed at run-time. It sounds complicated, but it really isn’t. Instead of having the statements typed directly into the stored procedure, the SQL statements are first built and defined in variables.

The code in these variables is then executed. Continuing with our example, here is the same code using dynamic SQL:

DECLARE @birthYear int = 1970
DECLARE @statement NVARCHAR(4000)

WHILE @birthYear <= 1971
BEGIN
   SET @statement = '
        SELECT   JobTitle, Count(BusinessEntityID)
        FROM     HumanResources.Employee
        WHERE    Year(BirthDate) = ' + CAST(@birthYear as NVARCHAR) +
      ' GROUP BY JobTitle'

   EXECUTE sp_executesql @statement
   SET @birthYear = @birthYear + 1
END

The dynamic SQL is highlighted in bold. This is the SQL that is built for each @birthYear. As the SQL is built, it is stored in @statement. It is then executed using sp_executesql, which we’ll explain below.

Introduction to sp_executesql

You can use sp_executeslq to execute transact SQL stored within a variable. The statement form is:

EXECUTE sp_executesql @statement.

In case you are wondering, sp_executesql is a system stored procedure. System stored procedures extend the language and provide more features for you to use.

Here is a simple example to try:

DECLARE @statement NVARCHAR(4000)
SET @statement = N'SELECT getdate()'
EXECUTE sp_executesql  @statement

If you run this in the query window, you’ll get a result similar to:

2018-01-24 18:49:30.143

Now that you’ve seen how sp_executeslq works, let’s put it to practice. Let’s assume you’ve been asked to write a stored procedure that returns either the average LineTotal or sum of LineTotal by ProductID for products shipped in 2011.

Your boss would like this written as a stored procedure. The stored procedure should accept one parameter @ReturnAverage. If true, then you’ll return the average, otherwise the sum.

Of course, you could write this as two separate queries as shown in the following stored procedure, but that wouldn’t be much fun, as it would be too much typing and prone to errors!

CREATE PROCEDURE uspCalcuateSalesSummaryStatic
@returnAverage bit
AS
IF (@returnAverage = 1)
BEGIN
   SELECT   SOD.ProductID,
            AVG(SOD.LineTotal) as ResultAvg
   FROM     Sales.SalesOrderDetail SOD
            INNER JOIN Sales.SalesOrderHEader SOH 
                       ON SOH.SalesOrderID = SOD.SalesOrderID
   WHERE    YEAR(SOH.ShipDate) = 2011
   GROUP BY SOD.ProductID
END
ELSE
BEGIN
   SELECT   SOD.ProductID,
            SUM(SOD.LineTotal) as ResultSum
   FROM     Sales.SalesOrderDetail SOD
            INNER JOIN Sales.SalesOrderHEader SOH 
                       ON SOH.SalesOrderID = SOD.SalesOrderID
   WHERE    YEAR(SOH.ShipDate) = 2011
   GROUP BY SOD.ProductID
END

The bad part here is there is a lot of duplicate code, which I’ve put in bold. There isn’t much unique code, but that there is, is in italics.

With all this redundancy, we’ve got a great opportunity to show off some dynamic SQL. Let’s go for it!

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic
                 @returnAverage bit
AS
DECLARE @statement NVARCHAR(4000),
@function NVARCHAR(10)
IF (@returnAverage = 1) SET @function = 'Avg'
ELSE SET @function = 'Sum'

SET @statement =
    'SELECT  SOD.ProductID,' +
             @function +  + '(SOD.LineTotal) as Result' + @function + '
     FROM    Sales.SalesOrderDetail SOD
             INNER JOIN Sales.SalesOrderHEader SOH
                        ON SOH.SalesOrderID = SOD.SalesOrderID
     WHERE    YEAR(SOH.ShipDate) = 2011
     GROUP BY SOD.ProductID'

EXECUTE sp_executesql @statement

Here, instead of having two complete versions of the SQL, one for AVG, the other for SUM, we build the requested version on-the-fly.

The SQL is built and saved into the variable @statement. This variable is built based on the parameter value @returnAverage. If set to 1, then @function represents the Average; otherwise, Summation.

You can see where the SQL is then built to create statement. Notice the color coding. It should correspond similar portions within the static version; this should help you do a comparison.

Debugging Dynamic SQL

You may be wondering what the SQL looks like at run time. You can easily inspect the code using the debugger:

Run the stored procedure using the debugger’s run command, and then step into the code.

Continue to Step Into the code until you read the Execute statement highlighted below.

Using the Debugger

Once you reach this statement, hover over the @statement, and when the tool tip is displayed, select text visualizer.

The debugger is powerful and worth understanding. I would highly encourage you learn more about it here.

Using sp_executesql with Parameters

You can use sp_executesql to reference parameters within your statement. This ultimately makes your code easier to read and provides some optimization benefits as the statement can be compiled once and reused many times.

The statement takes the form:

EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …

So let’s explain the pieces.

• @statement is the SQL we wish to execute.
• @parameterDefinition is a string containing a definition of all parameters referenced in @statement. Each parameter and type found @statement is listed. The name and type are separated by a space. Multiple parameters are separated by a comma.

Next, we set the parameter values, by specifying the parameters and desired value. The parameters are listed in order defined within the @parameterDefinition string.

• @parm1 is the first parameter defined within the @parameterDefinition string. Value is the value you wish to set it to.
• @parm2 is the second parameter, if defined, as declared in @parameterDefinition.
• and so on…

Here is a simple example, which adds two numbers, to try:

DECLARE @statement NVARCHAR(4000)
DECLARE @parameterDefinition NVARCHAR(4000)

SET @statement = N'SELECT @a + @b'
SET @parameterDefinition = N'@a int, @b int'

EXECUTE sp_executesql  @statement, @parameterDefinition, @a=10, @b=5

The various portions of the statement are color coded:

• @statement (bold) – notice it includes 2 parameters: @a and @b. Also notice these are not declared in TSQL. Rather, they are defined in the parameter definition.
• @parameterDefinition (italics) – each parameter listed is defined as type int.
• Parameter values (underline) – we set the parameters’ value here.

To wrap up, in this example, we have a dynamically executed SQL statement which adds two parameters.

These parameters are defined as integers. Each parameter’s value is set in the sp_executesql command.

Example using sp_executesql with Parameters

Let’s take our previous example and extend it. Rather than hardcoding the shipDate into the query as we did, let’s bring that in as a parameter. This makes the query more flexible and works with years other than 2011.

To make this change, we’ll add a parameter to our stored procedure, as well as the dynamic query. We’ll use the sp_executesql command to call the dynamic query using these parameters.

The updated stored procedure with changes is shown below. The stored procedure parameter is green, and the dynamic query parameter red.

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2
                 @returnAverage bit,
                 @shipDate int
AS
DECLARE @statement NVARCHAR(4000),
@parameterDefinition NVARCHAR(4000),
@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = 'Avg'
ELSE SET @function = 'Sum'

SET @parameterDefinition = '@shipDateYear int'
SET @statement =
    'SELECT   SOD.ProductID,' +
              @function +  + '(SOD.LineTotal) as Result' + @function + '
     FROM     Sales.SalesOrderDetail SOD
              INNER JOIN Sales.SalesOrderHEader SOH
                         ON SOH.SalesOrderID = SOD.SalesOrderID
     WHERE    YEAR(SOH.ShipDate) = @shipDateYear
     GROUP BY SOD.ProductID'

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDateYear=@shipDate

To run this, simply call the uspCalculateSalesSummaryDynamic2 proc from a query windows using the following command:

EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011

If you do so, you’ll see these results.

Let me show you one fine simplification, let’s combine @shipDateYear and @shipDate into one parameter. We’ll eliminate @shipDateYear from our code. This makes it easier to follow and read:

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2
                 @returnAverage bit,
 <span style="color: #339966;">                @shipDate int
AS
DECLARE @statement NVARCHAR(4000),
        @parameterDefinition NVARCHAR(4000),
        @function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = 'Avg'
ELSE SET @function = 'Sum'

SET @parameterDefinition = '@shipDate int'
SET @statement =
    'SELECT   SOD.ProductID,' +
              @function +  + '(SOD.LineTotal) as Result' + @function + '
     FROM     Sales.SalesOrderDetail SOD
              INNER JOIN Sales.SalesOrderHEader SOH
                         ON SOH.SalesOrderID = SOD.SalesOrderID
     WHERE    YEAR(SOH.ShipDate) = @shipDate
     GROUP BY SOD.ProductID'

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDate

Notice that the EXECUTE statement is much simpler, there is no need to assign the SQL statement parameter @shipDateYear to the stored procedure parameter @shipDate’s value.

This makes the statement more compact and easier to read. The flow seems to read better, as you don’t have to mentally make connections between the stored procedure parameters and SQL parameters.

Run Dynamic SQL with EXECUTE()

You can also use the EXEC or EXECUTE command to run dynamic SQL. The format for this command is:

EXECUTE (@statement)

Here is a simple example to try:

DECLARE @statement NVARCHAR(4000)
SET @statement = N'SELECT getdate()'
EXECUTE (@statement)

It is important to enclose @statement in parenthesis. If you don’t, the EXECUTE statement takes @statement, and instead of running the dynamic SQL, it thinks the variable value is the name of a stored procedure. You’ll get the following error:

Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'SELECT getdate()'.

Of course, this provides a great hint! If you wish, you can use variables to specify which stored procedures to call.

sp_executesql versus EXECUTE

You may be wondering why use sp_executesql versus EXECUTE. What are the differences between the two?

Here are several reasons why Microsoft recommends using sp_executesql to run dynamic SQL:

• With EXECUTE, all parameters much be converted from their native type to Unicode. This hampers the optimizer’s ability to match the dynamically built SQL with a pre-existing plan.
• By using sp_executesql, the optimizer recognizes the parameters within the dynamic SQL, making it easier for the optimizer to match plans.
• It is easier to read parameterized queries than it is to read a bunch of concatenated text which incorporates them.
• Parameterized queries are less prone to SQL injection attacks.

The post Build Dynamic SQL in a Stored Procedure appeared first on Essential SQL.