Execute Dynamic Query in SQL Server

Dynamic SQL is a statement constructed and executed at runtime, usually containing dynamically generated SQL string parts, input parameters, or both.

Various methods are available to construct and run dynamically generated SQL commands. The current article is going to explore them, define their positive and negative aspects, and demonstrate practical approaches to optimize queries in some frequent scenarios.

We use two ways to execute dynamic SQL: EXEC command and sp_executesql stored procedure.

Table of contents

• How to use EXEC or EXECUTE Command
• Examples of Using Extended Procedure sp_executesql
• Dynamic SQL in Stored Procedures
• OUTPUT Parameter in sp_executesql
• Protection Against SQL Injection with sp_executesql Procedure
• Feature Comparison of EXEC Command and sp_executesql Stored Procedure
• Conclusion

How to use EXEC or EXECUTE Command

For the first example, we create a simple dynamic SQL statement from the AdventureWorks database. The example has one filter that is passed through the concatenated string variable @AddressPart and executed in the last command:

USE AdventureWorks2019

-- Declare variable to hold generated SQL statement
DECLARE @SQLExec NVARCHAR(4000) 
DECLARE @AddressPart NVARCHAR(50) = 'a'

-- Build dynamic SQL
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''

-- Execute dynamic SQL 
EXEC (@SQLExec)

Note that queries built by string concatenation may give SQL injection vulnerabilities. I would strongly advise that you get familiar with this topic. If you plan to use this kind of development architecture, especially in a public-facing web application, it will be more than useful.

Next, we should handle are NULL values in string concatenations. For instance, the @AddressPart instance variable from the previous example could invalidate the entire SQL statement if passed this value.

The easiest way to handle this potential problem is to use the ISNULL function to construct a valid SQL statement:

SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + ISNULL(@AddressPart, ‘ ‘) + '%'''

Important! The EXEC command is not designed to re-use cached execution plans! It will create a new one for each execution.

To demonstrate this, we will execute the same query twice, but with a different value of input parameter. Then, we compare execution plans in both cases:

USE AdventureWorks2019

-- Case 1
DECLARE @SQLExec NVARCHAR(4000) 
DECLARE @AddressPart NVARCHAR(50) = 'a'
 
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''

EXEC (@SQLExec)

-- Case 2
SET @AddressPart = 'b'
 
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''

EXEC (@SQLExec)

-- Compare plans
SELECT chdpln.objtype
,      chdpln.cacheobjtype
,      chdpln.usecounts
,      sqltxt.text
  FROM sys.dm_exec_cached_plans as chdpln
       CROSS APPLY sys.dm_exec_sql_text(chdpln.plan_handle) as sqltxt
 WHERE sqltxt.text LIKE 'SELECT *%';

Examples of Using Extended Procedure sp_executesql

To use this procedure, we need to give it an SQL statement, the definition of parameters used in it, and their values. The syntax is the following:

sp_executesql @SQLStatement, N'@ParamNameDataType' , @Parameter1 = 'Value1'

Let’s start with a simple example that shows how to pass a statement and parameters:

EXECUTE sp_executesql  
               N'SELECT *  
                     FROM Person.Address
	       WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- SQL Statement
              N'@AddressPart NVARCHAR(50)',  -- Parameter definition
             @AddressPart = 'a';  -- Parameter value

Unlike EXEC command, the sp_executesql extended stored procedure re-uses execution plans if executed with the same statement but different parameters. Therefore, it is better to use sp_executesql over EXEC command:

EXECUTE sp_executesql  
               N'SELECT *  
                     FROM Person.Address
	       WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- SQL Statement
              N'@AddressPart NVARCHAR(50)',  -- Parameter definition
             @AddressPart = 'a';  -- Parameter value

EXECUTE sp_executesql  
               N'SELECT *  
                     FROM Person.Address
	       WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- SQL Statement
              N'@AddressPart NVARCHAR(50)',  -- Parameter definition
             @AddressPart = 'b';  -- Parameter value

SELECT chdpln.objtype
,      chdpln.cacheobjtype
,      chdpln.usecounts
,      sqltxt.text
  FROM sys.dm_exec_cached_plans as chdpln
       CROSS APPLY sys.dm_exec_sql_text(chdpln.plan_handle) as sqltxt
  WHERE sqltxt.text LIKE '%Person.Address%';

Dynamic SQL in Stored Procedures

Until now we used dynamic SQL in scripts. However, real benefits become apparent when we execute these constructs in custom programming objects – user stored procedures.

Let’s create a procedure that will look for a person in the AdventureWorks database, based on the different input procedure parameter values. From the user input, we will construct dynamic SQL command and execute it to return the result to calling user application:

CREATE OR ALTER PROCEDURE [dbo].[test_dynSQL]  
(
  @FirstName		 NVARCHAR(100) = NULL	
 ,@MiddleName        NVARCHAR(100) = NULL	
 ,@LastName			 NVARCHAR(100) = NULL	
)
AS          
BEGIN      
SET NOCOUNT ON;  
 
DECLARE @SQLExec    	NVARCHAR(MAX)
DECLARE @Parameters		NVARCHAR(500)
 
SET @Parameters = '@FirstName NVARCHAR(100),
  		            @MiddleName NVARCHAR(100),
			@LastName NVARCHAR(100)
			'
 
SET @SQLExec = 'SELECT *
	 	           FROM Person.Person
		         WHERE 1 = 1
		        ' 
IF @FirstName IS NOT NULL AND LEN(@FirstName) > 0 
   SET @SQLExec = @SQLExec + ' AND FirstName LIKE ''%'' + @FirstName + ''%'' '

IF @MiddleName IS NOT NULL AND LEN(@MiddleName) > 0 
                SET @SQLExec = @SQLExec + ' AND MiddleName LIKE ''%'' 
                                                                    + @MiddleName + ''%'' '

IF @LastName IS NOT NULL AND LEN(@LastName) > 0 
 SET @SQLExec = @SQLExec + ' AND LastName LIKE ''%'' + @LastName + ''%'' '

EXEC sp_Executesql @SQLExec
	         ,             @Parameters
 , @FirstName=@FirstName,  @MiddleName=@MiddleName,  
                                                @LastName=@LastName
 
END 
GO

EXEC [dbo].[test_dynSQL] 'Ke', NULL, NULL

OUTPUT Parameter in sp_executesql

            We can use sp_executesql with the OUTPUT parameter to save the value returned by the SELECT statement. As shown in the example below, this provides the number of rows returned by the query to the output variable @Output:

DECLARE @Output INT

EXECUTE sp_executesql  
        N'SELECT @Output = COUNT(*)
            FROM Person.Address
	       WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- SQL Statement
              N'@AddressPart NVARCHAR(50), @Output INT OUT',  -- Parameter definition
             @AddressPart = 'a', @Output = @Output OUT;  -- Parameters

SELECT @Output

Protection Against SQL Injection with sp_executesql Procedure

There are two simple activities you should do to reduce the risk of SQL injection significantly. First, enclose table names in brackets. Second, check in the code whether tables exist in the database. Both these methods are present in the example below.

We are creating a simple stored procedure and executing it with valid and invalid parameters:

CREATE OR ALTER PROCEDURE [dbo].[test_dynSQL] 
(
  @InputTableName NVARCHAR(500)
)
AS 
BEGIN 
  DECLARE @AddressPart NVARCHAR(500)
  DECLARE @Output INT
  DECLARE @SQLExec NVARCHAR(1000) 

  IF EXISTS(SELECT 1 FROM sys.objects WHERE type = 'u' AND name = @InputTableName)
  BEGIN

      EXECUTE sp_executesql  
        N'SELECT @Output = COUNT(*)
            FROM Person.Address
	       WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- SQL Statement
              N'@AddressPart NVARCHAR(50), @Output INT OUT',  -- Parameter definition
             @AddressPart = 'a', @Output = @Output OUT;  -- Parameters

       SELECT @Output
  END
  ELSE
  BEGIN
     THROW 51000, 'Invalid table name given, possible SQL injection. Exiting procedure', 1 
  END
END


EXEC [dbo].[test_dynSQL] 'Person'

EXEC [dbo].[test_dynSQL] 'NoTable'

Feature Comparison of EXEC Command and sp_executesql Stored Procedure

Conclusion

This post demonstrated two ways of implementing the dynamic SQL functionality in SQL Server. We’ve learned why it is better to use the sp_executesql procedure if it is available. Also, we’ve clarified the specificity of using the EXEC command and the demands to sanitize user inputs for preventing SQL injection.

For the accurate and comfortable debugging of stored procedures in SQL Server Management Studio v18 (and higher), you can use the specialized T-SQL Debugger feature, a part of the popular dbForge SQL Complete solution.