Dynamic SQL in SQL Server

Dynamic SQL is a programming technique where you build SQL query as a string and execute it dynamically at runtime. It lets you build the general-purpose query on the fly using variables, based on the requirements of the application. This makes a dynamic SQL more flexible as it is not hardcoded.

For example, the following is a dynamic SQL.

Example: Dynamic SQL

DECLARE @sql nvarchar(max) --declare variable
DECLARE @empId nvarchar(max) --declare variable for parameter

set @empId = '5' --assign value to parameter variable
set @sql = 'SELECT * FROM EMPLOYEE WHERE EMPID =' + @empId --build query string with parameter

exec(@sql) --execute sql query

In the above example, we first declare variables, @sql for SQL query and @empId for the parameter. Then, @empId = '5' statement assigns value to a variable. The set @sql = statement builds the SQL query as a string and appends parameter variable using + operator. Finally, exec(@sql) executes the @sql string and returns the result.