Dynamic SQL:
Dynamic SQL is a programming technique that allows you to construct SQL statements dynamically at runtime.
Creating a dynamic SQL is simple, you just need to make it a string as follows:
'SELECT * FROM production.products';
Code language: SQL (Structured Query Language) (sql)To execute a dynamic SQL statement, you call the stored procedure sp_executesql as shown in the following statement:
EXEC sp_executesql N'SELECT * FROM production.products';Code language: SQL (Structured Query Language) (sql)Because the sp_executesql accepts the dynamic SQL as a Unicode string, you need to prefix it with an N.
Example:
DECLARE
@table NVARCHAR(128),
@sql NVARCHAR(MAX);
SET @table = N'production.products';
SET @sql = N'SELECT * FROM ' + @table;
EXEC sp_executesql @sql;
Why do we need Dynamic SQL?
- We need to use Dynamic SQL for the following use cases:
- When we need to run dynamic queries on our database, mainly DML queries.
- When we need to access an object which is not in existence during the compile time.
- Whenever we need to optimize the run time of our queries.
- When we need to instantiate the created logic blocks.
- When we need to perform operations on application fed data using invoker rights.
