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.
HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
