Dynamic SQL - Build using Stored Procedures

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

All examples found in this lesson are based on the Microsoft SQL Server Management Studio and the sample databases from AdventureWorks and WideWorldImporters. Start learning SQL today using these free tools with my guide Getting Started Using SQL Server.

Build dynamic SQL in a stored procedure.

Many SQL we write is explicitly written into the stored procedure. This is what we call the static SQL. Such is named because it doesn’t change. Once it’s written, that means that it’s set-hammered into stone.

Below is an example of a 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

Did you notice that there are two statements here? Each statement returns a summary of JobTitles for a specific employee birth year. If we want to add more birth years, then 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 the dynamic SQL comes to into play.

Dynamic SQL is SQL your proc creates and executes as run-time. It sounds complicated, but it really isn’t. Instead of having the statements typed directly into the stored procedure, the procedure builds the SQL statements into defined variables.

Once complete, the database references the variable’s value and executes it as code. Now, let’s continue with our example, here is the same code using the 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 green. 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.

Are You New to Stored Procedures? If so, Checkout our Ultimate Guide to SPROCS!

Introduction to sp_executesql

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

EXECUTE sp_executesql @statement.

In case you are wondering, the sp_executesql is a system stored procedure. The 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 similar result like this:

2018-01-24 18:49:30.143

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

Your boss would prefer to have 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 proc 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

Simplify with Dynamic SQL

The bad part here is there is a lot of duplicate code, which I’ve colored green. There isn’t much unique code, but that there is, is colored red.

With all this redundancy, we’ve got a great opportunity 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 wonder 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.

Debugging Dynamic SQL — 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. If 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 parameters, if defines, as declared in @parameterDefinition.
and so on…

Here is a simple example, which add 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 (green) – 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 (blue) – each parameter listed is defined as type int.
Parameter values (red) – we set the parameters’ value here.

To wrap up, in this example we have a dynamically executed SQL statement which add 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 to 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,
                 @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 store 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 difference 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 hamper 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.

Now that we have your interest, why not check out these cool articles!

5 responses to “Dynamic SQL – Build using Stored Procedures”

Kevin Martin

May 8, 2022

You could give sp_CRUDGen a try. It is a stored procedure that generates kitchen sink (optional parameters) stored procedures based on your tables along with the foreign keys or views.

justMe

Feb 21, 2020

why are there 2 +’s in this statement?

‘SELECT SOD.ProductID,’ +
@function + + ‘(SOD.LineTotal)

1. Israel Alafe
  
  Dec 19, 2020
  
  I think it is a typo
  
Vetek

Jan 27, 2020

Hi Kris, thanks for sharing this knowledge. What do you think about hardcoding sql statements vs stored procedures?

anil kale

Dec 10, 2019

declare @var2 nvarchar(MAX)
set @var1 = ‘anil’
set @var4 = ‘1019518594’

set @var2 = N’select
a.*
from card b
join log a on a.Cust = b.ID
where a.c = ”’ + @var1 + ”’ and b.s =”’+ @var4 +””

Dynamic SQL – Build using Stored Procedures

Build dynamic SQL in a stored procedure.

Introduction to sp_executesql

Simplify with Dynamic SQL

Debugging Dynamic SQL

Using sp_executesql with Parameters

Example using sp_executesql with Parameters

Run Dynamic SQL with EXECUTE()

sp_executesql versus EXECUTE

5 responses to “Dynamic SQL – Build using Stored Procedures”

Leave a Reply Cancel reply

More from the blog

Recommended Topics

Popular Tags

Get Started Learning SQL!

Do You Want to Learn SQL?

Free Beginners SQL Course!

Dynamic SQL – Build using Stored Procedures

Build dynamic SQL in a stored procedure.

Introduction to sp_executesql

Simplify with Dynamic SQL

Debugging Dynamic SQL

Using sp_executesql with Parameters

Example using sp_executesql with Parameters

Run Dynamic SQL with EXECUTE()

sp_executesql versus EXECUTE

5 responses to “Dynamic SQL – Build using Stored Procedures”

Leave a Reply Cancel reply

More from the blog

Recommended Topics

Popular Tags