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'
EXEC(@sqlQry)

SQL

Copy

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)

SQL

Copy

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)

SQL

Copy

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)

SQL

Copy

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’

SQL

Copy

To execute the Dynamic SQL use

EXEC(@sqlQry)

SQL

Copy

To view how the dynamically generated query looks like use

Print(@sqlQry)

SQL

Copy

Summary

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