SQL server SP : sometimes not to be included in WHERE clause

Question

I am very new to SQL Server Stored Procedures, This is My SP :

CREATE PROCEDURE [dbo].[spGetBonusRunDetails]
(
  @StartDate as DATETIME,
  @EndDate as DATETIME,
   @PageNumber int,
  @PageSize int, 
   @Status int
)
AS

;WITH DataCTE AS
(
   SELECT ROW_NUMBER() OVER(Order by Id) as RowNumber
     ,[StartDateTime]
      ,[EndDate]
     ,[Status]
     ,[ExecutionTime]
     ,[Code] , TotalRows = Count(*) OVER() 
   FROM [dbo].[BonusRun]
    WHERE ((StartDateTime BETWEEN @StartDate AND @EndDate)  
   OR (EndDate BETWEEN @StartDate AND @EndDate))
   AND (Status = @Status)
 )

I want that @Status condition check sometimes not to be included in WHERE clause.
How to do that ?

Edit :

is it not possible to to write some thing

IF @Status <= 0
 then @Status = NULL 
END IF

and in where statement

AND (Status = @Status OR @Status IS NULL)

Answer 1

A commonly used approach is:

CREATE PROCEDURE [dbo].[spGetBonusRunDetails]
(
  @StartDate as DATETIME,
  @EndDate as DATETIME,
   @PageNumber int,
  @PageSize int, 
   @Status int = NULL
)
AS

;WITH DataCTE AS
(
   SELECT ROW_NUMBER() OVER(Order by Id) as RowNumber
     ,[StartDateTime]
      ,[EndDate]
     ,[Status]
     ,[ExecutionTime]
     ,[Code] , TotalRows = Count(*) OVER() 
   FROM [dbo].[BonusRun]
    WHERE ((StartDateTime BETWEEN @StartDate AND @EndDate)  
   OR (EndDate BETWEEN @StartDate AND @EndDate))
   AND (@Status IS NULL OR Status = @Status)
 )

But please be aware that this can have implications for Parameter Sniffing and the possibility of an inappropriate cached query plan.

In fact, while I always try not to use dynamic TSQL, when there are a large number of optional parameters, it is a valid approach to avoid incorrect cached plans.

Answer 2

The easiest way I've found to do this would be using a case statement. Might not be the most efficient solution, but its better than writing the query twice, once without the where clause (if performance isn't your biggest concern)

   SELECT ROW_NUMBER() OVER(Order by Id) as RowNumber
     ,[StartDateTime]
      ,[EndDate]
     ,[Status]
     ,[ExecutionTime]
     ,[Code] , TotalRows = Count(*) OVER() 
   FROM [dbo].[BonusRun]
    WHERE ((StartDateTime BETWEEN @StartDate AND @EndDate)  
   OR (EndDate BETWEEN @StartDate AND @EndDate))
   AND 
   (CASE WHEN (@Status IS NULL) THEN CAST(1 AS bit) ELSE Status = @Status END);

I think I got that right

Answer 3

AND (Status = ISNULL(@Status, Status))

In this case if @Status is NULL it will be ignored.

Answer 4

How about:

AND (@Status IS NULL OR @Status = Status)

So if @Status is NULL, you would return all types of Status - you could use a default value instead of NULL.