Dynamic Qurey in SQL Server

Dynamic query refers to SQL statements that are created and executed dynamically at runtime, rather than hardcoded into a program or stored procedure. This allows for flexible and customizable queries, where the query structure or conditions can change based on variables or circumstances.

Here is an example of how to use Dynamic query in SQL Server.

Suppose we have a table called Employees with columns EmpId,EmpName, Gender,  Salary, and DepartmentId. 

Here is the simple dynamic Query Created in the SQL Server,

DECLARE @Search NVARCHAR(100) = 'Female';DECLARE @SQL NVARCHAR(MAX);SET @SQL = 'SELECT * FROM Employees WHERE Gender = '''+@Search+''' '-- execute the above dynamic queryEXEC(@SQL);

Example-

In the example above-

there are three ‘ ’ are used with the @search variable, it is used for escaping the‘ ’ in dynamic query.

If you want to create a stored procedure that fetches employee information based on different criteria passed as parameters.

USE MyCollegeDbGOCREATE PROCEDURE GetEmployees (@EmpName NVARCHAR(50) = '')ASBEGINSET NOCOUNT ON;DECLARE @SQL NVARCHAR(MAX);SET @SQL = 'SELECT * FROM Employees WHERE EmpName LIKE ''%'+@EmpName+ '%'' 'PRINT @SQL-- execute the above dynamic queryEXEC(@SQL);SET NOCOUNT OFF;END

Execute the procedure- 

Explanation-

Procedure Definition
The GetEmployees option accepts optional parameters: @EmpName.

Dynamic SQL Construction (@sql)
We start with a base SQL query (SELECT … FROM Employees WHERE condition) and dynamically add conditions based on availableinput parameters.

Conditionals
If @EmpNameis provided, we add a condition to filter by Employee name

Execution (sp_executesql)
Finally, the concatenated SQL string (@sql) is executed using sp_executesql.
We pass parameter ( @EmpName) to sp_executesql using the format on the parameter.

When using dynamic SQL, it is important to consider security implications (such as SQL injection vulnerabilities) and performance due to query plan caching. Adoption of appropriate testing and investment policies is essential to mitigating these risks.

Also, Read: Explain the SQL triggers and their uses