//
you're reading...
SQL Server

sp_executesql Vs EXECUTE Command

 

A dynamically build Transact-SQL statements can be executed using EXECUTE Command or sp_executesql statement. Here, in this article in my examples, I’ll be using sp_executesql which is more efficient, faster in execution and also supports parameter substitution. If we are using EXECUTE command to execute the SQL String, then all the parameters should be converted to character and made as a part of the Query before execution. But the sp_executesql statement provides a better way of implementing this. It allows us to substitute the parameter values for any parameter specified in the SQL String. Before getting into the actual example, let me differentiate these two commands with a simple example. Say – selecting a record from the employee table using the ID in the WHERE clause.

The basic syntax for using EXECUTE command:

EXECUTE(@SQLStatement)

The basic syntax for using sp_executesql:

sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]

Example 1.0

/* Using EXECUTE Command */
/* Build and Execute a Transact-SQL String with a single parameter
value Using EXECUTE Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String with parameter value */
SET @SQLQuery = ‘SELECT * FROM tblEmployees WHERE EmployeeID = ‘ +
CAST(@EmpID AS NVARCHAR(10))
/* Execute Transact-SQL String */
EXECUTE(@SQLQuery)

In the above example 1.0, there are two variables declared. The first variable @EmpID is used as a parameter to the SQL Query and second Variable @SQLQuery is used to build the SQL String. You can clearly see that the variable@EmpID is cast to a NVarchar type and made as a part of the SQL String. If you print the @SQLQuery string (PRINT @SQLQuery), you will get the actual SQL query as shown below:

SELECT * FROM tblEmployees WHERE EmployeeID = 1001

Finally, the above query is executed using the EXECUTE command.

Example 1.1

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = ‘SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID’
/* Specify Parameter Format */
SET @ParameterDefinition =  ‘@EmpID SMALLINT’
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

In the example 1.1, there are two variables declared. The variable @EmpID is used as a parameter to the SQL Query and second variable @SQLQuery is used to build the SQL String, the third variable @ParameterDefinition is used to specify the parameter format before executing the SQL string. If you print the @SQLQuery string (PRINT @SQLQuery), you will get the query as shown below:

SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID

Here, in this example, you can clearly see the parameter @EmpID is included in the statement. Finally, sp_executesqltakes the necessary information to do the parameter substitution and execute the dynamically built SQL string.

  1. @SQLQuery –> contains the SQL statement
  2. @ParameterDefinition –> contains the Parameter Definition
  3. @EmpID –> contains the parameter value to be substituted to the parameter in the SQL statement.

NOTE: The parameters included in the Dynamic SQL string must have a corresponding entry in the Parameter Definition List and Parameter Value List.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: