Dynamic SQL – Part-1 – Big Data and SQL

The method we use to write SQL queries so that they are dynamically built alongside application actions is known as “dynamic SQL.”

In other words, With dynamic SQL, you can create software that makes use of SQL statements whose entire text won’t be known until runtime.

We can manage complex industrial applications and transactions with its assistance without incurring additional costs.

Dynamic SQL allows for the creation of flexible SQL queries, and when an application is executed, the names of any variables or other parameters are passed.

Below is an example of dynamic SQL.

DECLARE @SQL NVARCHAR(1000)
DECLARE @EmpNo varchar(50)
SET @EmpNo = ‘7662’
SET @SQL = ‘SELECT EmpNo, EName, DeptID FROM Emp where EmpNo = ‘+ @EmpNo
EXEC (@SQL)

This will generate and execute the following statement.
SELECT EmpNo, EName, DeptID FROM Emp where EmpNo = 7662

However, statements that can be stated more clearly and effectively will not be used in dynamic SQL.

Let’s look at some plausible scenarios. Assume there are hundreds of databases, and you want to regularly back up each one.

BACKUP DATABASE <YouDatabaseName> TO DISK = ‘Filepath’;

Instead of writing the statement for each and every database, you can develop a dynamic SQL that takes the database names from metadata and writes and executes the backup statements, in the following way.

DECLARE @query VARCHAR(2000)
DECLARE @BeginID INT
DECLARE @LastID INT
DECLARE @DB VARCHAR(100)
DECLARE @BackupFilePath NVARCHAR(200)
DECLARE @FileName NVARCHAR(200)

SET @BeginID = 1
SET @BackupFilePath = ‘C:\myWorksFolder\’

DECLARE @DBTables TABLE(Id INT IDENTITY, DBName VARCHAR(100))
INSERT INTO @DBTables
SELECT name FROM sys.databases WHERE name NOT IN(‘tempdb’,’model’)

SELECT @LastID = COUNT(*) FROM @DBTables

WHILE @BeginID <= @LastID
BEGIN
SELECT @DB = DBName FROM @DBTables WHERE Id = @BeginID
SET @FileName = @DB + ‘-‘ + CONVERT(VARCHAR(8), GETDATE(),112) + ‘.bak’
SET @query = ‘BACKUP DATABASE ‘ + @DB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFilePath + @FileName, ””)
EXEC(@query)
SET @BeginID = @BeginID + 1
END
GO