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'
EXEC(@sqlQry)
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
EXEC(@sqlQry)
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 '
EXEC(@sqlQry)
Dynamic SQL Using Table Using Filters
Declare @sqlQry varchar(4000)
Declare @age int=0
Declare @Salary decimal=0.0
Declare @designation varchar(50)=null
BEGIN
SET @sqlQry='Select empid,empname,age,salary,dob,designation from tblEmployee where 1=1'
IF @designation isnotnull
BEGIN
SET @sqlQry=@sqlQry+' AND designation ='''+@designation+''''
END
IF @age <> 0
BEGIN
SET @sqlQry=@sqlQry+' AND age ='+Convert(Varchar(2),@age)
END
IF @Salary <> 0.0
BEGIN
SET @sqlQry=@sqlQry+' AND Salary ='+Convert(Varchar(6),@Salary)
END
END
EXEC(@sqlQry)
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.
Example
Select * from tblemployee where designation=’Software engineer’
To execute the Dynamic SQL use
EXEC(@sqlQry)
To view how the dynamically generated query looks like use
Print(@sqlQry)
Summary
In this article, we have learned the basics of Dynamic SQL.