# Introduction to dynamic SQL
# 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](https://drive.google.com/drive/folders/19ZkH0XZqtwiwHAhlpheYY_9iF2C38QSt?usp=sharing).
## The ```EXEC``` command
```sql
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.
```sql
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.
```sql
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
```text
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.
```sql
-- 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.
```sql
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
```sql
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.
```sql
-- 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.
```sql
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
```sql
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.
```text
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.