SQL Select on Boolean three possible returns Ture,False,All

Question

I have a stored procedure I want to filter on a Bit field. If the user passes in a True return records with the Flag set to 1. If the user passes in a False only return records where the flag is 0. But if the Param is Null then return all the records regardless of the flag. I thought I could fool it with a Not = Null but that return a empty dataset. I want to return everting if the @WatchFlag is Null.

ALTER PROCEDURE [dbo].[p_ActInv_Rpt_r]   
	-- Add the parameters for the stored procedure here  
       (  
	    @ReportDate Date = NULL,  
	    @TypeList varchar(50) =  'LETTER,OUT_ENV,INSERT,BROCHURE,RET_ENV,PAPER',  
	    @WatchFlag varchar(5) =  Null   
	   )  
  
AS  
BEGIN  
SET NOCOUNT ON;  
SET @ReportDate = ISNULL(@ReportDate, GETDATE())  
  
SELECT Inventory.CompType,   
	   Inventory.CompName,   
	   Inventory.BDate,   
	   Inventory.EDate,   
	   Inventory.Box_Qty,   
	   Inventory.Reorder_Qty,   
	   Inventory.OnHand,   
	   Inventory.Boxes,   
       IsNull(Inventory.Location, ' ') AS Location,  
	   IsNull([v_CompCnts_View].[Sum of Counts],0) AS [Sum_of_Counts],   
	   Inventory.Watch_Flag,   
	   CASE [Watch_Flag] WHEN 1 THEN '****' ELSE '    ' END AS Watch_Display   
  FROM Inventory LEFT JOIN [v_CompCnts_View] ON Inventory.CompName = [v_CompCnts_View].PC_CompName   
 WHERE (Inventory.EDate>= @ReportDate)   
   AND (Inventory.CompType IN (SELECT value FROM STRING_SPLIT(@TypeList, ',')))  
   AND (Inventory.Watch_Flag <> (CASE WHEN @WatchFlag = 'True' THEN 0   
                                      WHEN @WatchFlag IS NULL THEN Null END)  
       )    
 ORDER BY Inventory.CompType, Inventory.Location DESC , Inventory.CompName;  
END  

Answer 1

Try this:

 ALTER PROCEDURE [dbo].[p_ActInv_Rpt_r]   
     -- Add the parameters for the stored procedure here  
        (  
         @ReportDate Date = NULL,  
         @TypeList varchar(50) =  'LETTER,OUT_ENV,INSERT,BROCHURE,RET_ENV,PAPER',  
         @WatchFlag varchar(5) =  Null   
        )  
      
 AS  
 BEGIN  
 SET NOCOUNT ON;  
 SET @ReportDate = ISNULL(@ReportDate, GETDATE())  
 DECLARE @bWatchFlag bit = NULL;  
  
 IF @WatchFlag = 'True'  
 SET @bWatchFlag = 1;  
 IF @WatchFlag = 'False'  
 SET @bWatchFlag = 0;  
  
      
 SELECT Inventory.CompType,   
        Inventory.CompName,   
        Inventory.BDate,   
        Inventory.EDate,   
        Inventory.Box_Qty,   
        Inventory.Reorder_Qty,   
        Inventory.OnHand,   
        Inventory.Boxes,   
        IsNull(Inventory.Location, ' ') AS Location,  
        IsNull([v_CompCnts_View].[Sum of Counts],0) AS [Sum_of_Counts],   
        Inventory.Watch_Flag,   
        CASE [Watch_Flag] WHEN 1 THEN '****' ELSE '    ' END AS Watch_Display   
   FROM Inventory LEFT JOIN [v_CompCnts_View] ON Inventory.CompName = [v_CompCnts_View].PC_CompName   
  WHERE (Inventory.EDate>= @ReportDate)   
    AND (Inventory.CompType IN (SELECT value FROM STRING_SPLIT(@TypeList, ',')))  
    AND (Inventory.Watch_Flag = @bWatchFlag OR @bWatchFlag IS NULL)  
           
  ORDER BY Inventory.CompType, Inventory.Location DESC , Inventory.CompName  
  OPTION (RECOMPILE);  
  
 END