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 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.