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!!!
Big Data and SQL 
One comment