Dynamic Queries with SQL

There has been many situations we had to use dynamic queries. There are different ways to write a dynamic queries. I would like to point out some of the advantages and disadvantages of using dynamic queries.


Most of the time the common mistake using dynamic queries is that we use EXEC sp_executesql and pass the query directly by replacing the values. This works fine but if we have any parameters that we need to use in query will be passed as part of the query and not as a parameter. This would internally keep seperate cache in sql server each time when you run the queries.

EXEC sp_executesql N'SELECT * FROM employees e where e.Employeeid=100'



This can be fine tuned as below

EXEC sp_executesql N'SELECT * FROM employees e where e.Employeeid = @id', N'@id int',@id=100

The sql will keep the query on canche and when the query invioked next time same execution plan will be used. In the earlier case, the sql will create seperate execution plan each time we run the query.

One of the other disadvantage using dynamic queries without passing the values parameters is that it might lead to SQL Injection. When the values are appended with sql and passed to sp_executesql it can lead to injection. Lets take an example with the above query



if the @id is passed to sp as "0 or 1=1" then the dynamic sql after replacing will be



EXEC sp_executesql N'SELECT * from employees e where e.employeeid =0 or 1=1'



This will return all the employees. This will be a high chance when we pass the parameter string. In the case of int it may throw error while passing value to sp itself. But all these can be avoided if we use parameterized dynamic sql.



I found a detailed article on dynamic sql http://www.sommarskog.se/dynamic_sql.html