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

William Burke 1 Reputation point
2022-06-29T17:45:45.177+00:00

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  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,256 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Tom Phillips 17,706 Reputation points
    2022-06-29T19:49:50.297+00:00

    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  
    
    0 comments No comments