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 SQL

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) and Print(@sqlQry) to view how the dynamically generated query looks like.

SUMMARY

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