Post
Ask Question

Executing Dynamic SQL Queries

Introduction

Dynamic SQL refers to SQL statements that are constructed at runtime rather than being hardcoded into an application. It allows for more flexibility and dynamism in SQL queries. Here are some key points about Dynamic SQL:

  • It allows you to create SQL statements dynamically based on input parameters or data values that are only known at runtime. The final SQL statement is not known until execution time.
  • It allows you to dynamically build SELECT, INSERT, UPDATE, DELETE statements, etc. at runtime.
  • It allows you to execute SQL statements directly from application code without having to hardcode the SQL.
  • It provides flexibility as the SQL statement can change based on business logic and input parameters.

Here are some examples.

Example 1. Basic Dynamic Query

Suppose you have a simple database table named "Products" with columns "ProductID," "ProductName," and "Price." You want to create a dynamic SQL query to retrieve product information based on user-defined search criteria.

DECLARE @ProductName NVARCHAR(50) = 'Widget';
DECLARE @MinPrice DECIMAL(10, 2) = 10.00;
DECLARE @MaxPrice DECIMAL(10, 2) = 50.00;
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Products WHERE 1=1';

IF @ProductName IS NOT NULL
    SET @SQL = @SQL + ' AND ProductName = @ProductName';

IF @MinPrice IS NOT NULL
    SET @SQL = @SQL + ' AND Price >= @MinPrice';

IF @MaxPrice IS NOT NULL
    SET @SQL = @SQL + ' AND Price <= @MaxPrice';

EXEC sp_executesql @SQL, N'@ProductName NVARCHAR(50), @MinPrice DECIMAL(10, 2), @MaxPrice DECIMAL(10, 2)',
                    @ProductName, @MinPrice, @MaxPrice;

Example 2. Table Name as a Variable

Suppose you need to perform similar operations on different tables based on user input, and the table name itself is a variable.

DECLARE @TableName NVARCHAR(50) = 'Customers';
DECLARE @City NVARCHAR(50) = 'New York';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE City = @City';

EXEC sp_executesql @SQL, N'@City NVARCHAR(50)', @City;

Example 3. Using Dynamic Cursors

Dynamic SQL can also be used to generate and execute cursor-related statements based on certain conditions.

DECLARE @CursorName NVARCHAR(50) = 'ProductCursor';
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ProductID INT, @ProductName NVARCHAR(100);

SET @SQL = 'DECLARE ' + QUOTENAME(@CursorName) + ' CURSOR FOR SELECT ProductID, ProductName FROM Products';

EXEC sp_executesql @SQL;

OPEN @CursorName;

FETCH NEXT FROM @CursorName INTO @ProductID, @ProductName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the data
    PRINT 'ProductID: ' + CONVERT(NVARCHAR(10), @ProductID) + ', ProductName: ' + @ProductName;

    FETCH NEXT FROM @CursorName INTO @ProductID, @ProductName;
END;

CLOSE @CursorName;

DEALLOCATE @CursorName;

Example 4. Basic Dynamic Query with Nested Subquery

Suppose you have two tables: "Orders" and "Customers," and you want to retrieve orders for a specific customer based on their name.

DECLARE @CustomerName NVARCHAR(100) = 'John Doe';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID IN (
        SELECT CustomerID
        FROM Customers
        WHERE CustomerName = @CustomerName
    )';

EXEC sp_executesql @SQL, N'@CustomerName NVARCHAR(100)', @CustomerName;

Example 5. Dynamic Query with Nested Subquery and Conditional Logic

Consider an example where you want to retrieve orders for a specific customer and optionally filter by order status.

DECLARE @CustomerName NVARCHAR(100) = 'Jane Smith';
DECLARE @OrderStatus NVARCHAR(50) = 'Shipped';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID IN (
        SELECT CustomerID
        FROM Customers
        WHERE CustomerName = @CustomerName
    )';

IF @OrderStatus IS NOT NULL
    SET @SQL = @SQL + ' AND OrderStatus = @OrderStatus';

EXEC sp_executesql @SQL, N'@CustomerName NVARCHAR(100), @OrderStatus NVARCHAR(50)',
                    @CustomerName, @OrderStatus;

Example 6. Dynamic SQL with Multiple Nested Subqueries

Let's say you want to retrieve a list of products along with their suppliers and categories, filtered by a specified category name.

DECLARE @CategoryName NVARCHAR(50) = 'Electronics';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
    SELECT p.ProductID, p.ProductName, s.SupplierName, c.CategoryName
    FROM Products p
    INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID
    INNER JOIN Categories c ON p.CategoryID = c.CategoryID
    WHERE p.CategoryID IN (
        SELECT CategoryID
        FROM Categories
        WHERE CategoryName = @CategoryName
    )';

EXEC sp_executesql @SQL, N'@CategoryName NVARCHAR(50)', @CategoryName;

Dynamic SQL provides the flexibility to adapt SQL queries to changing requirements or user inputs. Dynamic SQL with nested subqueries allows for the creation of complex and customizable queries based on runtime conditions. However, it also comes with some potential security risks, such as SQL injection if not handled carefully. Proper validation and sanitization of input parameters are crucial when using dynamic SQL to prevent these risks.


Similar Articles