Executing Dynamic SQL Using sp_executesql

There are times when an application needs to execute dynamic TSQL. Dynamic TSQL is SQL code that is generated by a program then executed. There are a number of different ways to execute dynamic TSQL code, but in this tip I will show you how to use the system stored procedure sp_executesql.

The following are advantages of using sp_executesql to execute dynamic TSQL:

• Allows you to pass parameters to your dynamic TSQL
• Can reduce SQL injection due to parameters being strongly typed
• Promotes plan reuse

The simplest form of executing some dynamic TSQL is to just execute TSQL code that is contained within a local variable and that requires no parameters. To demonstrate the following dynamic TSQL code will return a list of all the user tables in the current database:

DECLARE @DSQL nvarchar(100) = 'SELECT * FROM sys.tables';
  EXEC sp_executesql @DSQL;

There are times when your dynamic TSQL code requires parameters to be pass to it. To see how to pass parameters, review this TSQL code:

DECLARE @DSQL nvarchar(100) = 'SELECT * FROM sys.tables ' +
                              'WHERE name like ''%'' + @P1 + ''%''',
          @P1 nvarchar(100) = 'Staging';
EXEC sp_executesql @DSQL, N'@P1 nvarchar(10)', @P1=@P1;

In this code you can see that sp_executesql is called with three different parameters. The first parameter is just the dynamic TSQL code to be ran, which is defined in the local variable @DSQL. If you look at the string assigned to this local variable, you can see a parameter named “@P1” referenced. This variable is the name of the parameter to be passed to sp_executesql. Since the dynamic code references a parameter, the definition of this parameter had to be passed in the second parameter. If you look at the second parameter you can see the parameter @P1 is declared along with its data type, which is this case is a nvarchar(10). The third and final parameter to the sp_executesql is the actual name and value of the @P1 parameter.

For the last example let me show you how to pass multiple parameters to sp_executesqlL:

DECLARE @DSQL nvarchar(100) = 'SELECT * FROM sys.tables ' +
                              'WHERE name like ''%'' + @P1 + ''%''
                              or name like ''%'' + @P2 + ''%''', 
        @P1 nvarchar(100) = 'Staging',
        @P2 nvarchar(100) = 'ETL';
SELECT @DSQL
EXEC sp_executesql @DSQL,N'@P1 nvarchar(10), 
                           @P2 nvarchar(10)',
                   @P1=@P1,@P2=@P2;

In this code you can see two parameters, @P1 and @P2, are passed. The values for these two parameters are first set with a DECLARE statement. The second parameter of the sp_executesql statement defines the data types for the two parameters. The third parameter identifies the parameters being passed, and the local variables that are used to contain the values of these parameters. This example can be expanded to contain more parameters if your dynamic SQL code requires more parameters.

# # #

See All Articles by Columnist Gregory A. Larsen