Ranga Babu
temp table in dynamic SQL

Dynamic SQL in SQL Server

August 15, 2019 by

In this article, we will review how to construct and execute dynamic SQL statements in SQL Server with different examples.

Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed. Let us go through some examples using the EXEC command and sp_executesql extended stored procedure.

Executing dynamic SQL using EXEC/ EXECUTE command

EXEC command executes a stored procedure or string passed to it. Please refer to EXEC SQL overview and examples for more details and examples on the EXEC command.

The following example demonstrates constructing the SQL statement using the input variable and executing the SQL statement using the EXEC command.

There is a possibility of SQL injection when you construct the SQL statement by concatenating strings from user input values. I hope to cover the SQL injection and some methods to prevent SQL Injection in my future articles.

We should take care of null values when concatenating strings from parameters using ‘+’ operator. In the below example, I commented out the statement that sets a value to variable “@pid”.

By default, the variable “@pid” is NULL as we did not set any value. The final statement constructed after concatenation is blank as ‘+’ operator does not handle null values. Please refer to the below image that shows the final value of “@SQL” variable is blank.

dynamic SQL using EXEC

In this case, use the ISNULL function to construct a proper SQL statement while concatenating strings using ‘+’ operator.

EXEC command does not re-use the compiled plan stored in the plan cache. Execute the following query and check for the cached plans.

Please refer to the below image that shows two separate plans created when the above query is executed for two different parameters.

dynamic SQL cached plan

Executing dynamic SQL using sp_executesql

sp_executesql is an extended stored procedure that can be used to execute dynamic SQL statements in SQL Server. we need to pass the SQL statement and definition of the parameters used in the SQL statement and finally set the values to the parameters used in the query.

Following is the syntax of executing dynamic SQL statements using sp_executesql extended stored procedure.

Below example demonstrates executing dynamic SQL statement by passing parameters to sp_executesql extended stored procedure.

dynamic SQL using sp_executesql extended stored procedure

sp_executesql reuses the compiled plan when the statement is executed for different parameters. Execute the following query and check for the cached plan.

Please refer to the below image that shows the same plan is being used when the statement is executed with different parameters.

dynamic SQL cached plan

Using dynamic SQL inside stored procedures

Following is the example of using dynamic SQL inside a stored procedure. For demo purpose, I used the Product table from the AdventureWorksLT database. This stored procedure is used to search for products based on different columns like name, color, productid, and the product number. The dynamic SQL statement is constructed based on the input parameters passed to the stored procedure and is executed by the EXEC command.

When we execute the stored procedure with input parameter productid only, the SQL statement is constructed as shown in the below image.

dynamic SQL constructed SQL statement

Please refer to the below image that shows a different SQL statement constructed when productid and product number are passed as input parameters to the stored procedure.

dynamic SQL constructed SQL statement

Let us re-write the stored procedure to form dynamic SQL and execute it using sp_executesql extended stored procedure. Please refer to the below sample script.

Let us execute below sample thread that will retrieve all the products that are red.

dynamic SQL in stored procedure

OUTPUT parameter in sp_executesql

sp_executesql extended stored procedure supports the output parameter to store the value returned by the select query and use the output variable in another statement.

Following is the example script which shows the usage of the output variable in sp_executesql.

Temp tables in dynamic SQL

The local temp table created by executing dynamic SQL cannot be accessed outside the execution of dynamic SQL. It throws invalid object error as shown in the below image.

temp table in dynamic SQL

A workaround for this is to create the local temp table outside and use it in the dynamic SQL. Following is the example that demonstrates this scenario.

Please refer to the below image. we can see that the data is inserted in the temp table and can be accessed again.

temp table in dynamic SQL

Comparison of EXEC command and sp_executesql extended stored procedure

sp_executesql

EXEC Command

Reuses the cached plan

Generates multiple plans when executed with different parameters

Less prone to SQL Injection

Prone to SQL injection

Supports parameterization 

Does not support parameterization 

Supports output variable

Output variable is not supported

Conclusion

In this article, we explored how to construct and execute dynamic SQL in SQL Server using the EXEC command and sp_executesql extended stored procedure with different examples. In case you have any questions, please feel free to ask in the comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu

Latest posts by Ranga Babu (see all)

7,512 Views