Dynamic SQL in SQL Server

In SQL Server, at times the SQL Queries need to be dynamic and not static, meaning the complete SQL query may be built dynamically at run time as a string using the user inputs and any specific application logic. This can be done in queries run from back-end applications or inside stored procedures. In this article let us look into the details about how to create a dynamic SQL and its uses and also what are the security issues that may arise and how to handle those security issues.

What is a Dynamic SQL?

Generally, when we write a SQL Query, the SQL Statement or the Stored procedure SQL Statements are fully written as static statements which do not change at run time or once it is compiled initially. But there could be scenarios where the Table Name, Column Name list, the 'WHERE' clause values, or any part of the query may be generated at run time from user inputs. So, this technique of dynamically constructing and executing SQL statements at run time based on user inputs which helps to create flexible and adoptable queries is referred to as Dynamic SQL in SQL Server.

Building a dynamic SQL string can be determined by factors like string concatenation, parameter values, user input values, Stored procedure parameters, values received from calling a function or result from another subquery or any application logic.

Syntax:

Variable Name = SQL String

Explanation:

• Variable Name is the name of the variable where the Dynamic SQL is stored as a String.
  
• SQL String is the string that contains the SQL Statement using variables to represent dynamic elements like table names, column names, or conditions in the 'WHERE' clause.

Example of Dynamic SQL

Declare @sqlString nvarchar(2000),@strStudentGrade varchar(10)
Set @strStudentGrade= 'A'
Set @sqlString='Select * from Students Where Grade=' + char(39) + @strStudentGrade + char(39)
EXEC(@sqlString)

Output:

Explanation

The variable '@sqlString' holds the dynamic SQL created using the string concatenation and variables. Here the variable '@strStudentGrade' has the value for the Student Grade.

Once the dynamic SQL is built it can be run using the EXEC or sp_executesql commands. Above bexample uses the EXEC command.

The same can be run using sp_executesql command like below:

EXEC sp_executesql @sqlString

But the sp_executesql, a built-in system stored procedure is used mainly for parameterized queries.

Dynamic SQL for Dynamic Filtering

Dynamic Filtering is a method in SQL Server dynamic query to filter data for different filter conditions dynamically for the same SQL query. The Filter condition could be an user input received from the front-end and passed to a dynamic query.

Example of Dynamic Filtering

Create Procedure spFilterStudentData
@strStudentName nvarchar(100)
As
Begin
Declare @SqlQuery nvarchar(1000)
Set @SqlQuery= 'Select * from Students where Student_Name =' + CHAR(39) + @strStudentName + CHAR(39)
Exec(@SqlQuery)
End

This stored procedure can be run using the below execution command:

EXEC spFilterStudentData 'Ramesh'

Output:

Explanation: In the above example, the Student Name is filtered dynamically using a stored procedure with dynamic SQL to create the filter dynamically. When the stored procedure is executed with 'Ramesh' as filter data, the above result is displayed from the 'Students' table.

Dynamic SQL for Dynamic Sorting

Dynamic sorting using dynamic SQL in SQL Server is a method to sort one or more columns of a dynamic query result set dynamically and flexibly. By this method, the user can determine the sorting order of the result set at runtime instead of being hardcoded in the query.

Example of Dynamic Sorting

Create Procedure spSortStudentData
@strOrderBy nvarchar(30)
As
Begin
Declare @SqlQuery nvarchar(1000)
Set @SqlQuery= 'Select * from Students Order By ' +  @strOrderBy
Exec(@SqlQuery)
End

This stored procedure can be run using the below execution command:

Exec spSortStudentData 'Student_Name'

Output:

Explanation: In the above example the Student table data can be displayed by passing the column name by which the user wants to 'Sort' the data, and as an example the table data is sort by 'Student_Name' by sending this column name value when the stored procedure 'spSortStudentData' is executed.

Dynamic SQL for Schema Modifications

During run time Table Schema changes can be done using dynamic SQL based on user inputs or application logic. A typical Schema modification includes adding a new table or adding a new column to an existing table using dynamic SQL.

Create Procedure AddNewTableDynamicSQL
(
@TableName varchar(30),
@ColumnName1 varchar(30),
@ColumnName2 varchar(30),
@ColumnName3 varchar(30)
)
As 
Begin
Declare @SQLString nvarchar(1000)
Set @SQLString='Create Table ' + QUOTENAME(@TableName) + '(' + QUOTENAME(@ColumnName1) + 
'varchar(50),' + QUOTENAME(@ColumnName2) + 'varchar(50),'  + QUOTENAME(@ColumnName3) + 'varchar(50))'
EXEC sp_executesql @SQLString
End

User can execute the stored procedure as below to create a new table:

EXEC AddNewTableDynamicSQL  'Customers', 'CustID', 'CustomerName', 'CustomerLocation'

Another example of Adding the column to an existing table:

CREATE Procedure AddNewColumnDynamicSQL
(
@TableName varchar(30),
@ColumnName1 varchar(30),
@DataType varchar(30)
)
As 
Begin
Declare @SQLString nvarchar(1000)
Set @SQLString='ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD ' + QUOTENAME(@ColumnName1) + ' ' + @DataType
EXEC sp_executesql @SQLString
End

User can execute the stored procedure as below to create a add a new column to existing table:

EXEC AddNewColumnDynamicSQL 'Customers','CustomerPhone','varchar(50)'

Dynamic SQL for Parameterized Queries

Parameterized queries in Dynamic SQL is a method of building a SQL Query with parameters and this helps avoid security issues.

Declare @sqlquery nvarchar(1000),@sqlGrade char(1)
Set @sqlquery='Select * from Students where Grade=@eGrade' 
Set @sqlGrade='B'
EXEC sp_executesql @sqlquery, N'@eGrade char(1)', @eGrade =@sqlGrade
Here the 'Grade' value is passed as parameter while executing the dynamic sql statement.

Ouput:

Uses of Dynamic SQL

Dynamic SQL helps to create flexible, adaptable, and reusable SQL queries that can be applied to different scenarios and situations in fetching data from SQL Server.

Some of the general uses of Dynamic SQL are given below:

• Dynamic SQL allows to building of generic queries that can work with different tables, columns, and filtering conditions by using dynamic elements at runtime.
  
• Users can build dynamic queries based on user inputs and application logic to generate dynamic data and reports.
  
• Dynamic Filtering of Data can be done by creating dynamic SQL queries at runtime by user input for 'WHERE' clauses in a SQL query.
  
• Using the dynamic SQL queries users can sort data displayed dynamically from user interface inputs.

Dynamic SQL and Security Risks

When a SQL query is constructed using user input, this can lead to SQL injection attacks. So it is advised to use dynamic SQL with caution and check all data input by users to avoid any security risks. It is always advisable to use parameterized queries to prevent SQL injection attacks.

Conclusion

Dynamic SQL queries in SQL Server are a great option to generate dynamic and re-usable code which offers a lot of flexibility and avoid code repetition. But utmost care should be taken to avoids security issues since dynamic SQL generated using user input can lead to SQL injection attacks.