What Is Dynamic SQL

What is Dynamic SQL?

Dynamic SQL is about creating and running SQL Statements at run-time. We can store a SQL statement inside a variable and execute that statement.

It is for the most part used to compose broadly useful and adaptable projects where the SQL Statements will be made and executed at run-time in light of the prerequisite.

Why do we need Dynamic SQL?

Dynamic SQL is very helpful to dynamically set the filters, columns, and table names.

Downsides of Dynamic SQL

It's riskier because the SQL statements aren't parsed until runtime, so it's more difficult to catch simple syntax errors. Also, many attempts at dynamic SQL run into performance problems, and the complexity of simply writing dynamic SQL gives a negative impression on dynamic SQL.

How we Achieve Dynamic SQL?

We will achieve the Dynamic SQL by String concatenation and exec statement in SQL.

String concatenation means appending different strings together in strings that are the varchar data type.

Declare a varchar variable and append the string checking conditions and use EXEC Statement to execute that varchar variable.

Simple Dynamic SQ

Declare @sqlQry varchar(4000)
SET @sqlQry='Select empid,empname,age,salary,dob from tblEmployee'

Dynamic SQL Using Table Name as Dynamic

Declare @sqlQry varchar(4000)
Declare @tblName varchar(20)='tblEmployee'
SET @sqlQry='Select empid,empname,age,salary,dob from '+ @tblName

Dynamic SQL Using Column Name as Dynamic

Declare @sqlQry varchar(4000)
Declare @tblcol varchar(50)='empid,empname,age,salary,dob'
SET @sqlQry='Select '+ @tblcol +' from tblEmployee '

Dynamic SQL Using Table Using Filters

Declare @sqlQry varchar(4000)
Declare @age int=0
Declare @Salary decimal=0.0
Declare @designation varchar(50)=null
SET @sqlQry='Select empid,empname,age,salary,dob,designation from tblEmployee where 1=1'
IF @designation isnotnull
SET @sqlQry=@sqlQry+' AND designation ='''+@designation+''''
IF @age <> 0
SET @sqlQry=@sqlQry+' AND age ='+Convert(Varchar(2),@age)
IF @Salary <> 0.0
SET @sqlQry=@sqlQry+' AND Salary ='+Convert(Varchar(6),@Salary)

Note: In the above examples, we are appending values in the varchar variable so another type of variable needs to be converted as varchar. For varchar variables mention that variable in Triple Quotes like this '''+@designation+''' because in SQL we give string in between quotes.


Select * from tblemployee where designation=’Software engineer’

To execute the Dynamic SQL use


To view how the dynamically generated query looks like use



In this article, we have learned the basics of Dynamic SQL.

Similar Articles