Dynamic SQL

How to use Dynamic SQL?

The steps to use Dynamic SQL are given below:

1. Declare two variables: @var1 for the table name and @var2 for the dynamic SQL

2. Set table_name as the value for the @var1 variable.

3. By including the SELECT statement with the table name parameter, create dynamic SQL.

4. Use the @var2 parameter to execute the stored procedure sp_executesql.

Example of Using Dynamic SQL

Output:

Executing Dynamic SQL Using EXEC/ EXECUTE Command

A stored procedure or string provided to the EXEC command is executed. For more information and examples regarding the EXEC command, please refer to the EXEC SQL overview and examples.

The following instance shows how to create an SQL statement using an input variable and how to execute it by using the EXEC command.

SQL injection is possible when you create the SQL statement by joining strings from user input data.

We must consider null values when using the '+' operator to concatenate strings from arguments. In the example below, I commented on the line that assigns a value to the variable "@pid".

Since we did not set a value, the default variable "@pid" is NULL. Since the '+' operator does not support null values, the final statement created after concatenation is empty. The figure below demonstrates that the "@SQL" variable's final value is zero.

Use the ISNULL function to create a correct SQL statement while concatenating strings with the '+' operator.

The compiled plan kept in the plan cache is not used again by the EXEC command. Run the next query and look for any cached plans.

Please refer to the graphic below, which shows the two distinct plans generated when that query is run with two different parameters.

Executing Dynamic SQL Using sp_executesql

To execute dynamic SQL statements in SQL Server, use the extended stored procedure sp_executesql. We have to send the SQL statement, define the parameters utilized in the SQL statement, and then specify the values for the query parameters.

The syntax for using the extended stored procedure sp_executesql to execute dynamic SQL statements is listed below.

The example below shows how to use the extended stored procedure sp_executesql to execute a dynamic SQL statement.

When executing the statement with different arguments, sp_executesql reuses the compiled plan. Run the subsequent query and look for a cached plan.

Please refer to the figure below, which shows how the same plan is applied, no matter the parameters used to execute the statement.

Using Dynamic SQL Inside Stored Procedures

The example of using dynamic SQL inside of a stored procedure is given below. Products may be found using this stored procedure based on columns like name, color, productid, and product number. The EXEC command executes the dynamic SQL statement created depending on the input parameters supplied to the stored procedure.

The SQL statement is written as shown in the below image when we run the stored procedure with the input parameter productid alone.

Please refer to the image below, which shows how a distinct SQL statement is created when the stored procedure receives the productid and product number as input parameters.

Let's modify the stored procedure to create dynamic SQL and run it using the extended stored procedure sp_executesql. Please see the sample script below.

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

Temp Tables in Dynamic SQL

It is impossible to access the local temporary table produced by dynamic SQL outside of the dynamic SQL execution. As seen in the figure below, it throws an invalid object error.

Making a local temporary table outside and using it in the dynamic SQL is a fix for this. Here is an example of this scenario in action.

Refer to the picture below. We can see that the data has been added to the temporary table and is accessible once more.

EXEC Command and sp_executesql Extended Stored Procedure Comparison

Benefits of Dynamic SQL

Dynamic SQL can offer several benefits in certain situations:

1. Flexibility:
   Dynamic SQL allows you to construct SQL statements based on runtime conditions. This flexibility is particularly valuable when building complex queries with varying parameters, sorting options, or filtering conditions.
2. Parameterization:
   It enables you to easily parameterize SQL queries, which helps prevent SQL injection attacks. You can bind user inputs or variables to placeholders in the dynamically generated SQL statement, making it safer.
3. Dynamic Queries:
   You can create dynamic search queries, reporting tools, and data extraction utilities, common in applications where users can specify their search criteria or where the query structure is not known in advance.
4. Reduced Code Duplication:
   Dynamic SQL can help reduce code duplication because you can generate SQL statements programmatically rather than copying and pasting similar SQL queries with minor variations.
5. Performance Optimization:
   In some cases, dynamic SQL can improve query performance. You can conditionally include or exclude specific clauses (e.g., WHERE clauses) based on runtime conditions, potentially generating more efficient execution plans.
6. Simplified Maintenance:
   When database schemas change or new features are added, using dynamic SQL can make it easier to adapt your queries to these changes, as you only need to update the code that generates the SQL statements rather than altering multiple static queries scattered throughout your application.
7. Compatibility:
   Dynamic SQL can be useful when working with multiple database management systems (DBMS) with different SQL dialects. You can generate SQL statements compatible with the specific DBMS, making your application more portable.
8. Query Generation Abstraction:
   It provides an abstraction layer for query generation, making your code more organized and easier to maintain. You can encapsulate complex query construction logic in functions or classes.
9. Ad Hoc Reporting:
   Dynamic SQL is commonly used in ad hoc reporting tools, where users can build custom reports with varying criteria and columns without requiring predefined SQL queries for each possible report.
10. Testing and Debugging:
    Dynamic SQL can simplify testing and debugging by logging or displaying the generated SQL statements. This makes it easier to identify issues or optimize queries.