When do we use dynamic sql

EXEC command executes a stored procedure or string passed to it. There is a possibility of SQL injection when you construct the SQL statement by concatenating strings from user input values. EXEC command does not re-use the compiled plan stored in the plan cache.

Execute the following query and check for the cached plans. Please refer to the below image that shows two separate plans created when the above query is executed for two different parameters. Execute the following query and check for the cached plan. Please refer to the below image that shows the same plan is being used when the statement is executed with different parameters.

Following is the example of using dynamic SQL inside a stored procedure. This stored procedure is used to search for products based on different columns like name, color, productid, and the product number. The program in question might be running either on the client or application server debatable if you'd still call it 'dynamic' or within the database server. How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. What is a dynamic SQL query, and when would I want to use one? Ask Question. Asked 11 years, 8 months ago. Active 6 years, 11 months ago. Viewed 8k times. I'm using SQL Server Improve this question. Michael Petrotta Surya sasidhar Surya sasidhar 28k 55 55 gold badges silver badges bronze badges.

With that said, using this technique, we can write down any query, from the simplest possible to the most complex ones. You could use this technique outside SQL to build queries and send them for execution, or inside a stored procedure to build queries based on given parameters. The biggest advantage we have here is flexibility because you can control everything — what shall be in the SELECT and WHERE parts of the query, as well conditions — test variables to include or exclude conditions, adjust values of input variables based on certain criteria, etc.

An example of such a query is given below. If you write down a prepared statement in PHP or any other language the overall idea and syntax would be close to this example. This leads us to the conclusion that we could prepare statements in a programming language or at the database level. Therefore, we can do the following. If we want to go one step further, we can pass variables to stored procedures and build queries based on the values.

That stands for using these variables as parameters in the query, but we can also test variables and modify the query based on their value e.