Dynamic SQL

Dynamic SQL is a programming approach that allows you to create SQL statements at runtime. Because the whole wording of a SQL statement may be unknown at compilation, dynamic SQL allows you to design more general-purpose, adaptable applications.

Compilation ensures that the SQL statements refer to valid database objects and the compilation success validates that the appropriate privileges are in place to access the database objects.  However, the performance of static SQL is generally superior to that of dynamic SQL.

Static SQL statements do not alter between executions. At compilation, the whole text of static SQL statements is known.

Implementation:

To run a dynamic SQL statement, use the stored procedure sp_executesql, and add a prefix N with the function, as shown below:

EXEC sp_executesql N‘SELECT * FROM Emp’;

Simple dynamic SQL statement:
DECLARE @SQL NVARCHAR(100)
DECLARE @EmpNo VARCHAR(50)
SET @EmpNo = ‘7384’

SET @SQL = ‘SELECT EmpNo, EName, Job, HireDate FROM Emp where EmpNo = ‘+ @EmpNo
EXEC (@SQL)

Example2:

DECLARE @sqlCommand varchar(100)
DECLARE @columnList varchar(60)
DECLARE @tableName varchar(60)
DECLARE @job varchar(75)

SET @columnList = ‘EName, HireDate, Job, DeptNo’
SET @job = ”’Salesman”’
SET @sqlCommand = ‘SELECT ‘ + @columnList + ‘ FROM ‘ + @tableName + ‘ WHERE Job = ‘ + @job

EXEC (@sqlCommand)

Hope you find this article helpful.

Happy learning!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s