Of course!
Here’s a very detailed and lengthy explanation (targeting over 3000 words) on:

Handling Optional Parameters in SQL Server (and general databases)

1. Introduction to Optional Parameters

When writing stored procedures, user-defined functions, or executing dynamic SQL, optional parameters can add flexibility. They allow a single piece of SQL code to adapt its behavior based on input — either using provided parameters or falling back to default logic when parameters are missing.

Instead of writing several variations of a query to handle different inputs, optional parameters help consolidate and streamline query logic.

✅ Example of optional parameter use case:
A search form where users can filter by Name, Age, City, or any combination.

2. The Need for Optional Parameters

2.1 Real-world scenarios:

Search functionality: Users may search by some or none of the filters.
Reports generation: Based on provided filters like date range, customer ID, region, etc.
Admin dashboards: Filtering results optionally by user role, activity date, or status.

Without optional parameters:

You would have to write many different versions of the query.
Code would be hard to maintain and debug.
You risk performance hits due to non-optimized query paths.

3. Ways to Handle Optional Parameters

3.1 Use Default Parameter Values

You can set default values directly in stored procedures.

CREATE PROCEDURE GetEmployees
    @DepartmentID INT = NULL,
    @Location NVARCHAR(100) = NULL
AS
BEGIN
    SELECT * 
    FROM Employees
    WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID)
      AND (@Location IS NULL OR Location = @Location)
END

Explanation:

If @DepartmentID or @Location is NULL, the filter is ignored.
If they are provided, the corresponding condition is applied.

✅ Good: Very simple to implement.
❌ Downside: May impact performance because SQL Server cannot use indexes effectively when using OR.

3.2 Dynamic SQL

Dynamic SQL can be used to build queries based only on the provided parameters.

CREATE PROCEDURE GetEmployeesDynamic
    @DepartmentID INT = NULL,
    @Location NVARCHAR(100) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT * FROM Employees WHERE 1=1'

    IF @DepartmentID IS NOT NULL
        SET @SQL += ' AND DepartmentID = @DepartmentID'

    IF @Location IS NOT NULL
        SET @SQL += ' AND Location = @Location'

    EXEC sp_executesql @SQL, 
                       N'@DepartmentID INT, @Location NVARCHAR(100)', 
                       @DepartmentID, @Location
END

Explanation:

Start with a base query.
Append conditions only for provided parameters.
Use sp_executesql to allow parameterization (important for security and performance).

✅ Good: Optimized query plans, better use of indexes.
❌ Downside: Slightly complex to manage and debug.

3.3 CASE Expressions

Use CASE expressions inside WHERE clause:

SELECT *
FROM Employees
WHERE DepartmentID = CASE WHEN @DepartmentID IS NULL THEN DepartmentID ELSE @DepartmentID END
  AND Location = CASE WHEN @Location IS NULL THEN Location ELSE @Location END

✅ Good: Sometimes better than simple OR, but still might not use indexes efficiently.
❌ Downside: Increases complexity in SQL readability.

3.4 NULL Coalescing / ISNULL Functions

Using ISNULL() or COALESCE():

SELECT *
FROM Employees
WHERE DepartmentID = ISNULL(@DepartmentID, DepartmentID)
  AND Location = ISNULL(@Location, Location)

Risk Warning:

This method can cause performance issues if indexes are not designed properly.
SQL Server might not seek indexes effectively.

4. Key Design Strategies

4.1 Performance Considerations

Avoid WHERE conditions that always evaluate to true (1=1 tricks can hurt indexing).
Try to use dynamic SQL if you have several optional parameters.
Analyze execution plans using SQL Server Management Studio (SSMS).

4.2 Indexing Strategy

Since optional parameters may cause different execution paths:

Covering Indexes: Create indexes that cover multiple possible fields.
Filtered Indexes: Useful when certain filters are very common.

4.3 Parameter Sniffing Issues

When SQL Server caches a plan based on first executed parameters, it might be suboptimal for future calls.

✅ Solutions:

Use OPTION (RECOMPILE) to avoid reuse of bad execution plans.
Dynamic SQL automatically avoids parameter sniffing problems.

Example:

SELECT * FROM Employees
WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID)
OPTION (RECOMPILE)

5. Best Practices in Handling Optional Parameters

5.1 Clear Documentation

Always document which parameters are optional, their defaults, and their expected behavior.

5.2 Use Meaningful Defaults

Where possible, set business-logic-aware defaults instead of NULL.

Example:

Default a date filter to the current year.
Default an ID to a valid dummy ID (only if it’s harmless).

5.3 Protect Against SQL Injection

When building dynamic SQL, always parameterize queries via sp_executesql.
NEVER directly concatenate user input into SQL strings.

6. Advanced Topics

6.1 Dynamic Pivot Queries

When dynamic pivoting based on optional parameters, dynamic SQL is mandatory.

DECLARE @Columns NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

SELECT @Columns = STRING_AGG(QUOTENAME(Location), ',') 
FROM (SELECT DISTINCT Location FROM Employees) AS Locations

SET @SQL = '
SELECT DepartmentID, ' + @Columns + '
FROM (
    SELECT DepartmentID, Location, Salary
    FROM Employees
) as src
PIVOT (
    SUM(Salary)
    FOR Location IN (' + @Columns + ')
) as pvt'

EXEC sp_executesql @SQL

6.2 Optional JSON Parameters

In modern APIs, you might receive input as a JSON object with optional fields.

SQL Server supports JSON parsing:

DECLARE @JSON NVARCHAR(MAX) = '{"DepartmentID":2,"Location":"New York"}'

SELECT *
FROM Employees
WHERE (JSON_VALUE(@JSON, '$.DepartmentID') IS NULL OR DepartmentID = CAST(JSON_VALUE(@JSON, '$.DepartmentID') AS INT))
  AND (JSON_VALUE(@JSON, '$.Location') IS NULL OR Location = JSON_VALUE(@JSON, '$.Location'))

6.3 Optional Parameters in Functions

User-defined functions (UDFs) also support optional parameters:

CREATE FUNCTION GetEmployeeCount
(
    @DepartmentID INT = NULL
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Employees WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID))
END

7. Full Working Example

Here’s a complete scenario:

CREATE PROCEDURE SearchProducts
    @CategoryID INT = NULL,
    @PriceMin DECIMAL(10,2) = NULL,
    @PriceMax DECIMAL(10,2) = NULL,
    @Name NVARCHAR(255) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT ProductID, Name, CategoryID, Price FROM Products WHERE 1=1'

    IF @CategoryID IS NOT NULL
        SET @SQL += ' AND CategoryID = @CategoryID'

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

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

    IF @Name IS NOT NULL
        SET @SQL += ' AND Name LIKE ''%'' + @Name + ''%'''

    EXEC sp_executesql @SQL,
                       N'@CategoryID INT, @PriceMin DECIMAL(10,2), @PriceMax DECIMAL(10,2), @Name NVARCHAR(255)',
                       @CategoryID, @PriceMin, @PriceMax, @Name
END

Features:

Flexible
Safe (parameterized)
Dynamic
Scalable

8. Common Mistakes to Avoid

Mistake	Why it’s bad	How to fix
Using plain string concatenation	Risk of SQL injection	Always parameterize with `sp_executesql`
Not analyzing execution plans	May cause hidden performance issues	Always review query plans
Forgetting about NULL handling	Leads to wrong results	Properly test NULL scenarios
Overcomplicating with CASE	Harder to maintain	Prefer dynamic SQL when filters are many

9. Conclusion

Handling optional parameters correctly makes stored procedures and dynamic SQL more flexible, reusable, and maintainable.
Understanding the trade-offs between static query design and dynamic SQL is key for both performance and security.

When in doubt:

Use dynamic SQL for many optional filters.
Always parameterize.
Monitor and tune for performance.