Header Ads Widget

Sql Server Dynamic Query

Dynamic Query helps you to run SQL statements dynamically at runtime. It allows you to create more flexible SQL statements because the full text of the SQL statement is unknown at compilation.
Creating a dynamic SQL statement or query is simple
select  * from tblEmployee
if I go to create it dynamic 

EXEC sp_executesql N'select  * from tblEmployee'


var @customeQuery nvarchar(max)
set  @customeQuery=N'select  * from tblEmployee'
exec (customeQuery)

Dynamic SQL Query Required More Permission Than regular query 
To see the example  first remove read and write permission and execute Dyanamic query
its show error 

SELECT permission denied on object 'authors', database...............

and run the normal query it will execute

Executing unauthorized Code.
There is a high risk of executing unauthorized code while running a dynamic query, if someone put delete, update query between the line of query it is at high risk. So be careful from SQL injection 

To avoid sql injection you can use QUOTENAME()
set  @customeQuery=N'select  * from' +QUOTENAME(Schema)+'.'+QUOTENAME(tablename);

Post a Comment