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.

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.

The dynamic SQL query string can be executed using EXEC or EXECUTE command or using the sp_executesql stored procedure.

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 sp_executesql @sql

You can also build and execute SQL query string by directly passing the SQL query string to sp_executesql stored procedure, as shown below.

exec sp_executesql N'SELECT * FROM EMPLOYEE WHERE EMPID = @empId', 
N'@empid nvarchar(50)', @empId = '5'

The above would return the same result.

The following is the syntax to build and execute dynamic SQL using sp_executesql.

sp_executesql N'SQL query', N'@param1 data_type', @param1 = 'value1'

Dynamic SQL for DML statements

You can also build and execute dynamic SQL for DML statements like update and delete. The following example shows how to build the dynamic SQL for the UPDATE statement.

--declare variables
DECLARE @dynamicSQL nvarchar(max),
        @sal float,
        @empid int

-- assign values
set @sal = 53000 
set @empid = 5

-- declare string variables for parameters
DECLARE @salstr nvarchar(30),
        @empstr varchar(1000)

-- cast float and int parameters to string
SET @salstr  = CAST(@sal as nvarchar(30));
SET @empstr  = CAST(@empid as nvarchar(1000));

-- build dynamic upate statement
SET @sql = 'update Employee SET Salary = ' + @salstr +
            'WHERE EmpId = ' +  @empstr 

--execute dynamic statement
EXEC(@sql)

Dynamic SQL is about building SQL statements as a string and all variables are cast as string (nvarchar) variables. The above dynamic SQL updates the salary column of the Employee table in the database. We pass two variables, @sal and @empid to the UPDATE SQL string.@sal is a float data type and @empid is an integer data type. They are converted to string variables using the CAST statement before passing them to the SQL string.

Dynamic SQL in the Stored Procedure

You can convert the above dynamic SQL into a stored procedure as shown below. The stored procedure accepts two parameters, @sal and @empid

CREATE PROCEDURE uspDynamiDMLsql(@sal float, @empid int)
AS
BEGIN
	DECLARE @dynamicSQL nvarchar(max),
            @salstr nvarchar(30),
            @empstr varchar(1000)

        -- cast float and int parameters to string
        SET @salstr  = CAST(@sal as nvarchar(30));
        SET @empstr  = CAST(@empid as nvarchar(1000));

        -- build dynamic upate statement
        SET @sql = 'update Employee SET Salary = ' + @salstr +
                    'WHERE EmpId = ' +  @empstr 

        --execute dynamic statement
        EXEC(@sql)
END

You can execute the above stored procedure as follows:

EXEC uspDynamiDMLsql 53000, 5

Dynamic SQL for a DDL statement

Let's take the example of deleting a table using the DROP statement in the dynamic SQL.

In the following example, the table name is a variable that is passed to the SQL statement. This dynamic SQL is flexible and can be reused. It can be used to delete any table as it is input as a parameter and not hardcoded.

DECLARE @sql nvarchar(max),
        @tablename nvarchar(50)

SET @tablename = 'dbo.TableLog'
SET @sql = N'DROP TABLE ' + @tablename

EXEC(@sql)

Advantages of Using Dynamic SQL

• Dynamic SQL is flexible and can be reused by using parameters in building the query.
• Performance is improved as a better execution plan is generated.

Disadvantages of Using Dynamic SQL

• Hard to debug.
• Error management is difficult and unreliable.
• It is vulnerable to SQL injection and can cause security concerns.
• Dynamic SQL is slower than static SQL as SQL Server must generate an execution plan every time at runtime.
• Dynamic SQL requires the users to have direct access permission on all accessed database objects like tables and views.