Handle nullable parameters and dynamic WHERE clause in stored procedure [duplicate]

Question

I have a SQL Server stored procedure with several parameters that I'm using to build a dynamic WHERE clause. This stored procedure works fine, but the performance is terrible when certain parameters are included, especially when there are large tables involved.

Here is an example of the stored procedure:

ALTER PROCEDURE [dbo].[myTestProcedure]
    @Start_Date smalldatetime,
    @End_Date smalldatetime,
    @User varchar(71),
    @Template varchar(100),
    @Status varchar(400),
    @Office varchar(100),
    @GroupId int,
    @TraceOption int,
    @NotesOption int,
    @Type int,
    @FatherId int,
    @OfficeIds officeIds READONLY

-- SELECT excluded as it's huge
WHERE (p.AppointmentDate BETWEEN @Start_Date AND @End_Date)
  AND ((p.officeId IN (SELECT OfficeId FROM @OfficeIds)) OR 
       (SELECT COUNT(OfficeId) FROM @OfficeIds) = 0)
  AND (@GroupId = 0 OR g.GroupId = @GroupId)
  AND (@User IS NULL OR Forename + ' ' + Surname = @Valuer)
  AND (@Template IS NULL OR t.TemplateName = @Template)
  AND (@Status IS NULL OR d.Status = @Status)
  AND (@Office IS NULL OR a.Name = @Office)
  AND (@TraceOption = 0 
       OR (@TraceOption = 1 AND p.traceDate < GETDATE()-3)
       OR (@TraceOption = 2 AND p.traceDate >= GETDATE())
       OR (@TraceOption = 3 AND p.traceDate IS NULL))
  AND (@NotesOption = 0 
       OR @NotesOption = 2 
       AND (SELECT COUNT(*) 
            FROM Events 
            WHERE PrevId = p.Id 
              AND (DataItem = 14 OR DataItem = 54)) = 0
       OR (@NotesOption = 1 AND 
           (SELECT COUNT(*) 
            FROM Events 
            WHERE PrevId = p.Id AND (DataItem = 14 OR DataItem = 54)) > 0))
       AND (@Type = 0 
            OR (@Type = 2 AND p.Flags & 2 = 2) 
            OR (@Type = 1 AND p.Flags & 2 <> 2))

When only the date range filter (@Start_Date and @End_Date) is applied, the query executes very quickly, around 5 seconds (for 1 month data).

However, when additional filters are added (e.g., @User, @Template, @Status, etc.), the performance deteriorates drastically, and the query needs more than 2 minutes (for 1 month data).

I need to optimize this by only including filters in the WHERE clause if the associated parameter has a value, i.e., skip the check if the parameter is NULL, 0, or has no value.

Is there a way to conditionally include or exclude certain WHERE conditions based on whether the parameter has a valid value (NULL, 0, or no value)?

Is there a better way to handle this to optimize performance when there are many optional parameters in a large query?

Answer 1

As mentioned in comment by @Dale, you can follow a conditional approach to either include or exclude the parameters in WHERE clause

Following this you can build your dynamic sql as below

--BEGIN

DECLARE @sql_statement NVARCHAR(MAX);

SET @sql_statement = 'SELECT * FROM t WHERE 1=1'; 

-- Conditionally add filters
IF @Start_Date IS NOT NULL AND @End_Date IS NOT NULL
  SET @sql_statement += ' AND AppointmentDate BETWEEN @Start_Date AND @End_Date';

IF @User IS NOT NULL
  SET @sql_statement += ' AND Forename + '' '' + Surname = @User'; --you mentioned Valuer,not sure about it

IF @Template IS NOT NULL
  SET @sql_statement += ' AND TemplateName = @Template';

IF @Status IS NOT NULL
  SET @sql_statement += ' AND Status = @Status';

--and similarly you can add other parameters