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.
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
SET @columns = 'Customer_ID, Customer_Name'
SET @ct = '''CC'''
SET @sql = 'SELECT ' + @columns + ' FROM customers WHERE CUSTOMER_TYPE = ' + @ct
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
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM ' + @tableName;
EXEC sp_executesql @sql;
EXEC getTableRecords 'customers'