Dynamic SQL example in t-SQL

Dynamic SQL example in T-SQL often involves constructing complex queries that incorporate multiple tables, conditional logic, and parameterization to ensure both flexibility and security. Below is an example that demonstrates these concepts. This example involves dynamically constructing a query to retrieve data from multiple tables based on various conditions and parameters, while also preventing SQL injection.

Scenario

Let's say we have a database with the following tables:

• Employees
• Departments
• Projects

We want to create a dynamic SQL query that:

1. Retrieves employee details.
2. Includes department names if a specific flag is set.
3. Filters results based on employee status and department ID.
4. Joins with the Projects table if a project filter is provided.

Tables Structure

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DepartmentID INT,
    Status NVARCHAR(20)
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50)
);

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName NVARCHAR(50),
    EmployeeID INT
);

Dynamic SQL Example

DECLARE @IncludeDepartment BIT = 1
DECLARE @Status NVARCHAR(20) = 'Active'
DECLARE @DepartmentID INT = 2
DECLARE @IncludeProjects BIT = 1
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Params NVARCHAR(MAX)

-- Start constructing the SQL query
SET @SQL = N'SELECT E.EmployeeID, E.FirstName, E.LastName, E.Status'

-- Conditionally include department information
IF @IncludeDepartment = 1
BEGIN
    SET @SQL = @SQL + N', D.DepartmentName'
END

-- Join the Employees table
SET @SQL = @SQL + N' FROM Employees E'

-- Conditionally join the Departments table
IF @IncludeDepartment = 1
BEGIN
    SET @SQL = @SQL + N' LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID'
END

-- Add WHERE clause for filtering by status and department
SET @SQL = @SQL + N' WHERE E.Status = @Status AND E.DepartmentID = @DepartmentID'

-- Conditionally join the Projects table and add filter
IF @IncludeProjects = 1
BEGIN
    SET @SQL = @SQL + N' AND EXISTS (SELECT 1 FROM Projects P WHERE P.EmployeeID = E.EmployeeID)'
END

-- Define the parameters
SET @Params = N'@Status NVARCHAR(20), @DepartmentID INT'

-- Execute the dynamic SQL
EXEC sp_executesql @SQL, @Params, @Status = @Status, @DepartmentID = @DepartmentID

Explanation

1. Conditional Columns and Joins: The query dynamically includes the DepartmentName column and joins with the Departments table if @IncludeDepartment is set to 1. Similarly, it includes a condition to join with the Projects table if @IncludeProjects is set to 1.

2. Parameterization: The query uses parameters (@Status and @DepartmentID) to safely include user inputs and prevent SQL injection.

3. Complex Logic: The query incorporates conditional logic to construct different parts of the SQL statement based on the provided parameters.

4. Execution: The sp_executesql stored procedure is used to execute the constructed SQL statement with the defined parameters.

This example demonstrates how to build a flexible and secure dynamic SQL query that can adapt to various runtime conditions while maintaining readability and security.