Introduction

Several RDBMSs for example, PostgreSQL allow you to create a batch of SQL code that can be executed. In this article, I'll explain what Dynamic SQL is. Furthermore, I'll demonstrate its usefulness, and finally, I'll provide an example of how not to use dynamic SQL in SQL Server.

When to use Dynamic SQL

• Dynamic SQL is very useful in scenarios where the SQL statements that have to be executed by application are unknown at the time of execution.
• It can also be used for automating administrative tasks like querying metadata and constructing and executing a BACKUP DATABASE statement for each database in an on-prem instance.
• Dynamic SQL ameliorates the performance of some tasks, for example, by constructing parametrised ad-hoc queries that can benefit from previously cached execution plans.
• It is also used to construct elements of code based on querying actual data, for example, constructing a PIVOT query dynamically when you don't know ahead of time which elements should appear in the IN clause of the PIVOT operator.

Executing Dynamic SQL

In SQL Server, dynamic SQL can be executed using two commands namely:

• EXEC
• sp_executesql

The latter is the preferred command because it is more secure, flexible, and possesses an interface; that is it supports both input and output parameters. Let's take a few examples using both commands. The dataset used in this article can be found by executing the SQL files here.

The EXEC command

USE BikeStores;
GO

-- Declare a variable to reference the sql statement
DECLARE @my_sql AS VARCHAR(500);

-- Create the sql statement
SET @my_sql = '
select order_id, item_id,
  SUM(list_price) over(partition by order_id) as totalListPrice
from sales.order_items
order by order_id 
offset 0 rows fetch first 1 row only;';

-- execute the sql statement 
EXEC(@my_sql);

The sp_executesql Stored Procedure

The ability of sp_executesql to support input and output parameters can improve the security and efficiency of your code. In terms of security, parameters that appear in the code cannot be considered part of the code - they can only be considered operands in expressions. This eliminates your exposure to SQL injection attacks.

sp_executesql can perform better than the EXEC because its parametrisation aids in reusing cached execution plans.

Here's the syntax for using the sp_executesql procedure. All expressions delimited by the square braces are optional.

sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]

You provide a Unicode character string as the first parameter. You can optionally assign the SQL statement to @stmt variable. Secondly, you provide a Unicode character string holding the declarations of input and output parameters in the second input parameter or you can optionally assign the declarations to the @params variable. Lastly, you specify the assignments of input and output parameters separated by commas. Let's take a few examples.

DECLARE @sql AS NVARCHAR(200);

SET @sql = N'SELECT order_id, customer_id, order_date 
FROM sales.orders 
WHERE order_id = @order_id;';

EXEC sp_executesql 
  @stmt = @sql,
  @params = N'@order_id AS INT',
  @order_id = 1;

This should return the following output

order_id    customer_id     order_date
       1            259     2016-01-01

Here's a more complex example that uses output parameters and the OFFSET-FETCH filter.

-- Declare a variable to reference the sql statement
declare @sql AS NVARCHAR(500);

declare @totalPrice AS FLOAT;

set @sql = N'
select
  @totalPrice = sum(list_price) over(partition by order_id) 
from sales.order_items
where order_id = @my_order_id
order by order_id 
offset 0 rows fetch first 1 row only;
';

exec sp_executesql @sql, N'@my_order_id AS INT, @totalPrice AS FLOAT OUT', @my_order_id = 1, @totalPrice = @totalPrice OUTPUT;

select @totalPrice as totalPrice;

Notice that I didn't use the @stmt or @params.

Typical use case (Advanced)

When pivoting data in SQL Server, you need to know ahead of time which values to specify in the IN clause of the PIVOT operator. For example, run the following code to create a relation and insert some data into it.

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
   DROP TABLE dbo.Orders;


CREATE TABLE dbo.Orders 
(
    orderid INT NOT NULL,
    orderdate DATE NULL,
    empid INT NULL,
    custid VARCHAR(5) NULL,
    qty INT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);


INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty)
VALUES 
    (30001, '20070802', 3, 'A', 10),
    (10001, '20071224', 2, 'A', 12),
    (10005, '20071224', 1, 'B', 20),
    (40001, '20080109', 2, 'A', 40),
    (10006, '20080118', 1, 'C', 14),
    (20001, '20080212', 2, 'B', 12),
    (40005, '20090212', 3, 'A', 10),
    (20002, '20090216', 1, 'C', 20),
    (30003, '20090418', 2, 'B', 15),
    (30004, '20070418', 3, 'C', 22),
    (30007, '20090907', 3, 'D', 30);

Now let's do some pivoting

SELECT empid, A, B, C, D 
FROM (SELECT empid, custid, qty 
      FROM dbo.Orders) AS D 
    PIVOT(SUM(qty) FOR custid IN (A, B, C, D)) AS P

You notice that we needed to specify all the unique custid values. But what if there are too many categories to count? For example what if there are A, B, C, D, E, F,... and so on. We can use dynamic SQL to find all the unique categories and concatenate these categories to an sql batch statement. The example below summaries the total amount spent by each customer since 2016.

-- Summarise the amount spent by each customer since 2016
create view sales.purchased_items_view
as 
select o.customer_id, oi.item_id, oi.list_price, o.order_date from sales.order_items as oi
inner join sales.orders o
  on oi.order_id = o.order_id;

go

-- Pivot the data
select * 
from (select customer_id, year(order_date) as orderyear, list_price
      from sales.purchased_items_view) as D 
pivot (sum(list_price) for orderyear in ([2016], [2017])) as P;

go

The IN clause contains 2016 and 2017, but what if some customers made orders in 2018? Let's try using a cursor and dynamic SQL to find out.

declare 
  @sql       as nvarchar(1000),
  @orderyear as int,
  @first     as int;

declare c cursor fast_forward for 
  select distinct (year(order_date)) as orderyear 
  from sales.purchased_items_view 
  order by orderyear;

-- regulate the addition of commas
set @first = 1;

-- create your batch statement
set @sql = N'
select * 
from (select customer_id, year(order_date) as orderyear, list_price
      from sales.purchased_items_view) as D 
pivot (sum(list_price) for orderyear in (';

open c;

fetch next from c into @orderyear;

while @@fetch_status = 0 
begin 
  if @first = 0
    set @sql = @sql + N','
  else 
    set @first = 0;

  set @sql = @sql + quotename(@orderyear);

  fetch next from c into @orderyear;
end

close c;

deallocate c;

set @sql = @sql + N')) as p;';

exec sp_executesql @sql;

We can see that in fact, some customers purchased some items in 2018.

How NOT to use Dynamic SQL

Additional parameters specified in the sp_executesql procedure must contain only constants or variables. The values cannot be more complex expressions such as functions, or expressions built by using operators. For example, this will not work

declare @sql as nvarchar(100) = N'select * from @table_name';
exec sp_executesql @sql, N'@table_name as nvarchar', @table_name=N'sales.orders';

You get slammed by the following error.

Msg 1087, Level 16, State 1, Line 1
Must declare the table variable "@table_name".

Conclusion

In this article, I talked about dynamic SQL and the different ways to run dynamic SQL statements. I highlighted the use of the EXEC statement and the sp_executesql stored procedure. I provided a real-world use case of the dynamic SQL with the PIVOT operator, and lastly, I demonstrated how not to use dynamic SQL.