case statement in where clause.

Question

I have a stored procedure with the following code.

 alter PROCEDURE [dbo].[usp_Product_GetProductByCategoryID] 


@CategoryID uniqueidentifier,
@IsCategory bit=0  
AS  
  BEGIN  

   SELECT     ProductId, ProductCode, ProductName, MRP, MaxDiscount, 
              ShortDescription, ThumbNailImagePath,ThumbNailImagePath1, 
              FullImagePath, IsActive, NoOfBuyer,   
              LongDescription, BrandID, SubCategoryID, CreatedDate,
              LargeThumbnailImagePath, VideoPath  

   FROM         TBM_Product  

   WHERE (case @IsCategory 
           when 0 then  TBM_Product.SubCategoryID
           when 1 then  TBM_Product.CategoryID 
       end) 
            = @CategoryID 

   *AND (case 
       when @IsCategory= 0 then TBM_Product.SubCategoryID = TBM_Product.SubCategoryID
       when @IsCategory= 1 then TBM_Product.SubCategoryID is null 
      end)* 

END

what I want is

if(@IsCategory= 1) then

   TBM_Product.SubCategoryID is null

else

 void this and condition

how to achieve this.I am getting "incorrect syntax near =" error while compiling the above stored procedure.

Answer 1

SELECT...CASE doesn't work in WHERE conditions, and they don't work like you've written it. You'll have to modify your query and write the condition like this -

...
WHERE
    (@IsCategory= 0 AND TBM_Product.SubCategoryID = TBM_Product.SubCategoryID)
    OR
    (@IsCategory= 1 AND TBM_Product.SubCategoryID IS NULL)

Answer 2

try

and ((@Iscategory=1 and TBM_Product.SubCategoryID is null)
    or
    (@Iscategory = 0))

Answer 3

There is no need to use a case statement in your where clause. Instead use the AND/OR operators.

ALTER PROCEDURE [dbo].[usp_Product_GetProductByCategoryID] 
    @CategoryID uniqueidentifier ,
    @IsCategory bit = 0  
AS  
BEGIN  

SELECT  [ProductId], 
     [ProductCode], 
     [ProductName], 
     [MRP], 
     [MaxDiscount],    
     [ShortDescription],   
     [ThumbNailImagePath],
     [ThumbNailImagePath1], 
     [FullImagePath], 
     [IsActive], 
     [NoOfBuyer],   
        [LongDescription], 
     [BrandID], 
     [SubCategoryID], 
     [CreatedDate],  
     [LargeThumbnailImagePath], 
     [VideoPath],
     @CategoryID = CASE @IsCategory 
         WHEN 0 THEN  TBM_Product.SubCategoryID
         WHEN 1 THEN  TBM_Product.CategoryID 
          END 
FROM    [dbo].[TBM_Product]
WHERE   (@IsCategory = 0 AND TBM_Product.SubCategoryID = TBM_Product.SubCategoryID)
     OR (@IsCategory= 1 AND TBM_Product.SubCategoryID IS NULL)
END