Sql Server Dynamic Query

Introduction

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'

or 

var @customeQuery nvarchar(max)

set  @customeQuery=N'select  * from tblEmployee'

exec (customeQuery)

Security

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()

eg 

set  @customeQuery=N'select  * from' +QUOTENAME(Schema)+'.'+QUOTENAME(tablename);