Dynamic SQL

Dynamic SQL is a technique where SQL statements are built and executed at runtime instead of being fixed. It allows queries to change based on user input, conditions, or program logic, making applications more flexible and powerful when working with databases.

It is useful when:

• Table names or column names are not known in advance
• Filters depend on user input
• Queries need to change dynamically

The basic syntax for Dynamic SQL involves creating a query string and executing it using tools like sp_executesql in SQL Server:

Syntax:

EXEC sp_executesql N'SELECT statement';

The N prefix indicates that the SQL statement is treated as a Unicode string.

Static SQL vs Dynamic SQL

The table below highlights the key distinctions between Static SQL and Dynamic SQL for better understanding.

Steps to use Dynamic SQL

Follow these steps to create and execute Dynamic SQL in SQL Server:

1. Declare Variables: Declare two variables, @var1 for holding the name of the table and @var 2 for holding the dynamic SQL :

DECLARE 
@var1 NVARCHAR(MAX), 
@var2 NVARCHAR(MAX);

2. Assign Values to Variables: Set the value of the @var1 variable to table_name :

SET @var1 = N'table_name';

3. Construct the SQL Statement: Create the dynamic SQL by adding the SELECT statement to the table name parameter :

SET @var2= N'SELECT * 
FROM ' + @var1;

4. Execute the SQL Statement: Run the sp_executesql stored procedure by using the @var2 parameter :

EXEC sp_executesql @var2;

Example of Dynamic SQL

Dynamic SQL is a technique where SQL statements are built and executed at runtime instead of being fixed. It allows queries to change based on user input, conditions, or program logic, making applications more flexible and powerful when working with databases.

The following script dynamically retrieves data from the geek table:

Query:

DECLARE @tab NVARCHAR(128);
DECLARE @st NVARCHAR(MAX);

SET @tab = N'geektable';
SET @st = N'SELECT * FROM ' + QUOTENAME(@tab);

EXEC sp_executesql @st;

Output:

Advantages of Dynamic SQL

Here are some key advantages of dynamic SQL:

• Flexibility: Dynamic SQL creates queries that can change based on different conditions.
• User Input: It allows queries to be built using user-provided values.
• Dynamic Schema: It works with tables or columns that are created or changed at runtime.

Disadvantages of Dynamic SQL

Here are some disadvantages of dynamic SQL:

• Performance: Dynamic SQL can be slower because queries are built at runtime.
• Security: Poor handling of user input can lead to SQL injection risks.
• Debugging: It is harder to find errors since queries change while running.

Best Practices for Using Dynamic SQL

These tips help make Dynamic SQL safe and efficient:

• Use Parameterized Queries: Use sp_executesql to prevent SQL injection.
• Minimize Usage: Prefer static SQL when possible because it is faster and easier to maintain.
• Sanitize Inputs: Always validate user input to keep queries safe and secure.