how to dynamic filtering to sql query

​What is Dynamic Filtering in SQL?

Dynamic filtering is the process of creating and applying flexible filter conditions to SQL queries based on user input, program logic, or runtime conditions. Instead of hardcoding specific filter criteria into a query, dynamic filtering allows the query to adjust and adapt to changing requirements, making it more versatile and reusable.

Key Characteristics of Dynamic Filtering

1. Adaptability
   Dynamic filtering adapts to different inputs or scenarios without needing to rewrite the SQL query.
2. Runtime Application
   Filter conditions are determined at runtime, based on user choices, form submissions, or other dynamic factors.
3. Efficiency
   By applying only relevant filters, dynamic filtering avoids overloading the query with unnecessary conditions.
4. Reusability
   A single query or stored procedure can handle multiple use cases by varying the applied filters.

Why Use Dynamic Filtering?

Dynamic filtering is especially useful in scenarios like:

• User-Driven Reports: Where users can choose which fields to filter by.
• Custom Dashboards: Displaying data based on user-selected criteria.
• Search Functionality: Handling variable input fields (e.g., search by name, city, or department).
• Multi-Faceted Data Exploration: Allowing flexible and granular filtering options.

Dynamic Filtering vs. Static Filtering

How Does Dynamic Filtering Work?

Dynamic filtering works by including conditions in the SQL query only when relevant. This can be implemented in different ways depending on the database and application requirements.

1. Conditional Logic in Queries

Add conditions dynamically based on inputs:

SELECT * 
FROM Employees
WHERE (City = 'Boston' OR 'Boston' IS NULL)
  AND (Age > 30 OR 30 IS NULL);

If City is NULL, the condition (City = 'Boston' OR 'Boston' IS NULL) always evaluates to TRUE, effectively skipping the filter.

2. Query Construction in Code

Construct the query dynamically in the application layer:

filters = {"City": "Boston", "Age": None}
query = "SELECT * FROM Employees WHERE 1=1"

for column, value in filters.items():
    if value is not None:
        query += f" AND {column} = '{value}'"

print(query)
# Output: SELECT * FROM Employees WHERE 1=1 AND City = 'Boston'

3. Dynamic SQL Execution

Use stored procedures or scripts to dynamically build and execute the query:

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM Employees WHERE 1=1'

IF @City IS NOT NULL
    SET @sql += ' AND City = @City'

EXEC sp_executesql @sql, N'@City NVARCHAR(50)', @City = 'Boston'

Advantages of Dynamic Filtering

1. Flexibility: Handles variable filter conditions without requiring query rewrites.
2. User-Focused: Provides tailored data based on user preferences.
3. Code Reusability: A single query can serve multiple filtering scenarios.
4. Scalability: Easily extends to support new filters or criteria.

Challenges of Dynamic Filtering

1. Complexity: Query generation logic can become complex with many filters.
2. Performance Overhead: Dynamic conditions may increase execution plan generation time.
3. SQL Injection Risk: Improperly sanitized inputs can lead to vulnerabilities.
4. Debugging Difficulty: Dynamically generated queries may be harder to debug.

Examples of Dynamic Filtering in Action

Example 1: Dynamic Search for a Product Catalog

Users can filter products by category, price range, or availability.

Query Template:

SELECT * 
FROM Products 
WHERE (Category = @Category OR @Category IS NULL)
  AND (Price BETWEEN @MinPrice AND @MaxPrice OR @MinPrice IS NULL)
  AND (Availability = @Availability OR @Availability IS NULL);

Example 2: Dynamic Dashboard Filters

A report dashboard allows users to filter data by date range, region, or department.

Implementation in SQL Server:

CREATE PROCEDURE GetFilteredData
    @StartDate DATE = NULL,
    @EndDate DATE = NULL,
    @Region NVARCHAR(50) = NULL,
    @Department NVARCHAR(50) = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM Sales WHERE 1=1'

    IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
        SET @sql += ' AND SaleDate BETWEEN @StartDate AND @EndDate'
    IF @Region IS NOT NULL
        SET @sql += ' AND Region = @Region'
    IF @Department IS NOT NULL
        SET @sql += ' AND Department = @Department'

    EXEC sp_executesql @sql, 
        N'@StartDate DATE, @EndDate DATE, @Region NVARCHAR(50), @Department NVARCHAR(50)',
        @StartDate, @EndDate, @Region, @Department
END

Usage:

EXEC GetFilteredData @StartDate = '2023-01-01', @EndDate = '2023-12-31', @Region = NULL, @Department = 'IT';