Unleashing the Power of Dynamic SQL

This blog showcases how to create a dynamic SQL stored procedure in SQL Server for generating custom sales reports based on user-defined inputs for the start and end dates and product categories. The stored procedure uses dynamic SQL to aggregate and select data from the sales and products tables and returns the results in a format suitable for a report.

Hello Techies, 

Dynamic SQL is a technique that allows for the generation and execution of SQL statements at runtime based on unknown parameters or conditions until runtime. One way to implement dynamic SQL is through a stored procedure, which is a pre-written set of SQL statements that can be called and executed repeatedly.

This blog will discuss dynamic SQL stored procedures and provide an example of how they can be used.

Creating a Dynamic SQL Stored Procedure

It would be best if you first defined the input parameters and output variables to create a dynamic SQL stored procedure. These will be used to pass in the dynamic SQL statement and any additional parameters needed. For example:

CREATE PROCEDURE dynamic_sql_sp
  @sql_statement nvarchar(max),
  @param1 int,
  @param2 varchar(50),
  @output_table table (
    id int,
    name varchar(50)
  ) OUTPUT
AS
BEGIN
  -- Dynamic SQL statement to be executed
  DECLARE @sql nvarchar(max)
  SET @sql = '
    SELECT id, name
    FROM my_table
    WHERE col1 = @param1
    AND col2 = @param2
  '
  
  -- Execute dynamic SQL statement with input parameters
  EXEC sp_executesql @sql, N'@param1 int, @param2 varchar(50)', @param1, @param2
  
  -- Insert results into output table
  INSERT INTO @output_table (id, name)
  SELECT id, name
  FROM my_table
  WHERE col1 = @param1
  AND col2 = @param2
END

In this example, the dynamic SQL statement is defined as a string variable (@sql), and the sp_executesql stored procedure is used to execute the statement with the input parameters (@param1 and @param2). The results are then inserted into an output table variable (@output_table).

Using a Dynamic SQL Stored Procedure

To use the dynamic SQL stored procedure, you need to call it with the appropriate input parameters. For example:

DECLARE @results table (
  id int,
  name varchar(50)
)

EXEC dynamic_sql_sp 
  @sql_statement = '',
  @param1 = 1,
  @param2 = 'example',
  @output_table = @results OUTPUT

SELECT * FROM @results

In this example, the dynamic SQL statement is left blank (@sql_statement = ''), as it is defined within the stored procedure itself. The input parameters (@param1 and @param2) are specified, and an output table variable (@results) is declared to receive the stored procedure results.

Let's Take another example and create a stored procedure with dynamic SQL queries to understand better how it is implemented.

Let's consider an example of a dynamic SQL stored procedure that generates a report of sales data for a given time period and product category. The stored procedure will take input parameters for the start and end dates and the product category. It will generate a report of total sales revenue and quantity sold for each product in the specified category.

Here is the code for creating the dynamic SQL stored procedure:

CREATE PROCEDURE sp_sales_report
	@start_date DATE,
	@end_date DATE,
	@product_category NVARCHAR(50)
AS
BEGIN
	DECLARE @sql NVARCHAR(MAX)
	SET @sql = '
		SELECT 
			p.product_name,
			SUM(s.sales_quantity) AS total_quantity,
			SUM(s.sales_quantity * p.unit_price) AS total_revenue
		FROM 
			sales s
			INNER JOIN products p ON s.product_id = p.product_id
		WHERE 
			s.sales_date >= @start_date 
			AND s.sales_date <= @end_date
			AND p.product_category = @product_category
		GROUP BY 
			p.product_name
		ORDER BY 
			total_revenue DESC
	'

	EXEC sp_executesql @sql, N'@start_date DATE, @end_date DATE, @product_category NVARCHAR(50)', @start_date, @end_date, @product_category
END

In this stored procedure, the input parameters are the sales report's start and end dates and the product category. Next, the dynamic SQL statement selects the product name, total quantity sold, and total revenue for each product in the specified category and time period. Finally, the sales and products tables are joined on the product ID, and the results are grouped by product name and ordered by total revenue in descending order.

Let's now break down the SQL statement used in the stored procedure into multiple snippets for easier understanding:

SELECT 
	p.product_name,
	SUM(s.sales_quantity) AS total_quantity,
	SUM(s.sales_quantity * p.unit_price) AS total_revenue
FROM 
	sales s
	INNER JOIN products p ON s.product_id = p.product_id
WHERE 
	s.sales_date >= @start_date 
	AND s.sales_date <= @end_date
	AND p.product_category = @product_category
GROUP BY 
	p.product_name
ORDER BY 
	total_revenue DESC

• The SELECT clause selects the product name, total quantity sold, and total revenue for each product in the specified category and time period. Then, the SUM function calculates the total quantity sold and total revenue by multiplying the sales quantity by the product unit price.
• The FROM clause joins the sales and products tables on the product ID.
• The WHERE clause filters the sales data based on the start and end dates and the product category.
• The GROUP BY clause groups the results by product name, which allows us to calculate the total quantity sold and total revenue for each product.
• The ORDER BY clause orders the results by total revenue in descending order.

Now, let's create sample tables and data to test the stored procedure:

CREATE TABLE products (
	product_id INT PRIMARY KEY,
	product_name NVARCHAR(50),
	product_category NVARCHAR(50),
	unit_price DECIMAL(10, 2)
)

INSERT INTO products VALUES
(1, 'Product A', 'Category 1', 10.00),
(2, 'Product B', 'Category 2', 20.00),
(3, 'Product C', 'Category 1', 30.00),
(4, 'Product D', 'Category 2', 40.00)

CREATE TABLE sales (
	sales_id INT PRIMARY KEY,
	product_id INT,
	sales_date DATE,
	sales_quantity INT
)

INSERT INTO sales VALUES
(1, 1, '2023-04-01', 10),
(2, 2, '2023-04-02', 5),
(3, 1, '2023-04-03', 20),
(4, 3, '2023-04-04', 15),
(5, 4, '2023-04-05', 8),
(6, 2, '2023-04-06', 12),
(7, 1, '2023-04-07', 6),
(8, 3, '2023-04-08', 18),
(9, 4, '2023-04-09', 10),
(10, 2, '2023-04-10', 14)

First, this creates sample sales data for the month of April 2023 for the four products in the products table.

Now, let's test the stored procedure by running the following query:

EXEC sp_sales_report @start_date = '2023-04-01', @end_date = '2023-04-30', @product_category = 'Category 1'

This will generate a report of the total sales revenue and quantity sold for each product in Category 1 for the month of April 2023. The output should be:

product_name    total_quantity    total_revenue
-------------   --------------   -------------
Product A       30                300.00
Product C       15                450.00

As we can see, the stored procedure correctly calculates the total sales revenue and quantity sold for each product in Category 1 during the specified time period.

Conclusion

Dynamic SQL stored procedures can be a powerful tool for generating and executing SQL statements at runtime. By defining the SQL statement as a string variable and using sp_executesql to execute it with input parameters, you can create a flexible and reusable stored procedure that can be called and executed with different parameters and conditions.

I hope this could be helpful for you all. All the best for the future.