---
title: "Explain the Dynamic SQL Query with example in SQL Server."  
description: "Dynamic query refers to SQL statements that are created and executed dynamically at runtime, rather than hardcoded into a program or stored procedure."  
author: "Ashutosh Patel"  
published: 2024-07-10  
updated: 2024-07-11  
canonical: https://www.mindstick.com/articles/336382/explain-the-dynamic-sql-query-with-example-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

# Explain the Dynamic SQL Query with example in SQL Server.

#### Dynamic Qurey in SQL Server

[Dynamic query](https://www.mindstick.com/interview/914/how-can-you-create-dynamic-query) refers to SQL statements that are created and executed dynamically at runtime, rather than hardcoded into a program or [stored procedure](https://www.mindstick.com/forum/12886/stored-procedure-error-transaction-count-mismatch). 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](https://www.mindstick.com/forum/160921/help-with-writing-a-recursive-query-in-sql-server) 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](https://www.mindstick.com/articles/12620/use-of-in-operator-in-sql-server),

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

## Example-

![Explain the Dynamic SQL Query with example in SQL Server.](https://www.mindstick.com/mindstickarticle/975cb2c5-488e-49fd-a48d-cf578a2910c4/images/537fea6c-642d-4155-8104-3d275358b81f.png)

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

```plaintext
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-

![Explain the Dynamic SQL Query with example in SQL Server.](https://www.mindstick.com/mindstickarticle/975cb2c5-488e-49fd-a48d-cf578a2910c4/images/21637737-a957-4144-a859-8469a903b0b5.jpg)

## Explanation-

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

**[Dynamic SQL](https://www.mindstick.com/interview/623/what-is-dynamic-sql) Construction (**`@sql`**)**\
We start with a base SQL query (`SELECT … FROM Employees WHERE condition`) and dynamically add conditions based on available**input** parameters.

**Conditionals**\
If `@EmpName`is 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](https://www.mindstick.com/forum/160054/how-to-pass-parameter-values-in-javascript-arrow-function) ( `@EmpName`) to `sp_executesql` using the format on the parameter.

\
When using dynamic SQL, it is important to consider security [implications](https://yourviews.mindstick.com/view/85228/autonomous-vehicles-the-rise-of-self-driving-cars-and-their-implications) (such as [SQL injection](https://www.mindstick.com/forum/160895/protect-sql-server-database-against-sql-injection-attacks) vulnerabilities) and performance due to [query plan](https://www.mindstick.com/forum/160282/how-does-the-sql-server-query-optimizer-determine-the-optimal-query-plan-for-a-given-query) caching. Adoption of appropriate testing and investment policies is essential to mitigating these risks.

**Also, Read:** [Explain the SQL triggers and their uses](https://www.mindstick.com/articles/336344/explain-the-sql-triggers-and-their-uses)

---

Original Source: https://www.mindstick.com/articles/336382/explain-the-dynamic-sql-query-with-example-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.