dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted July 8, 2019

Executing Dynamic SQL Using sp_executesql

By Greg Larsen

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



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM