What is Dynamic SQL?

Dynamic SQL is a feature of the SQL Server database that allows you to build SQL statements dynamically at runtime.
Static SQL statements remain static during the runtime of the application while dynamic SQL executes commands in runtime for the application process.

Although, Static SQL is considered a robust and high-performance giving programming technique. But there come some stages or challenges which can be done effectively with dynamic SQL due to its flexibility and adaptability. In dealing with non-uniformly organized data, dynamic SQL is preferred.

It is evident from the nature of Dynamic SQL that it is compiled at the runtime. This means the database is also accessed at the runtime of the application. That attribute makes it less efficient in terms of performance but gives a more flexible approach to tackle certain problems which are quite hard to handle with static programming methods. Only this property is not a difference between both programming techniques.

Creating Dynamic SQL Statements

It is simple to create Dynamic SQL statements just like a static way of writing commands. For example, for a string below:
SELECT * FROM customers;

The stored procedure sp_executesql is called to execute a dynamic SQL statement. As described below:
EXEC sp_executesql N'SELECT * FROM customers';

Since the sp_executesql takes the dynamic SQL as a Unicode string, so it is needed to prefix it with an N.

Declare Dynamic SQL

Declare statement is also explained below through a table example using dynamic SQL.
Two variables are declared at first, @table which holds the name of the table to query is wanted and @sql for holds the dynamic SQL statement.
The second step is to set the value of table set @table variable to customers table.
The next step is to concatenate the SELECT statement with the table name parameter, Dynamic SQL constructed.
The last step is to use the sp_executesql stored procedure by passing the @sql parameter.

DECLARE
@table NVARCHAR(128),
@sql NVARCHAR(MAX);
SET @table = N' customers';
SET @sql = N'SELECT * FROM ' + @table;
EXEC sp_executesql @sql;

Dynamic SQL with parameters

DECLARE @ct varchar(10)
SET @ct = 'CC'
SELECT * FROM customers WHERE CUSTOMER_TYPE = @ct;

Dynamic SQL with EXEC

DECLARE
@sql nvarchar(max),
@columns varchar(250),
@ct varchar(10);
SET @columns = 'Customer_ID, Customer_Name'
SET @ct = '''CC'''
SET @sql = 'SELECT ' + @columns + ' FROM customers WHERE CUSTOMER_TYPE = ' + @ct
EXEC (@sql)

Dynamic SQL inside stored procedures

The example below shows how to use Dynamic SQL inside a SQL Server database storage procedure. The getRecordsTable stored procedure is used to return the records of a table specified in the input parameter. The EXEC command is used to call the procedure.

CREATE PROCEDURE getTableRecords
@tableName nvarchar(150)
AS
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM ' + @tableName;
EXEC sp_executesql @sql;
GO
EXEC getTableRecords 'customers'
GO