sql simple sproc - filter

Question

Hi,

I have a table called tblProducts with 3 columns: intID, dateMain, intStatus.

I have a stored procedure like so:

ALTER PROCEDURE [dbo].[spList_Report]
  @id INT,
  @startDate DATETIME = NULL,
  @endDate DATETIME = NULL,
  @includeStatus1 BIT,
  @includeStatus2 BIT,
  @includeStatus3 BIT,
  @includeStatus4 BIT

AS
  SET NOCOUNT ON

  SELECT *
  FROM
    tblProducts as products
  WHERE 
    product.intID = @id
    AND product.dateMain >= @startDate 
    AND product.dateMain <= @endDate

What I would like to know is how could I add to this to include rows based on the BIT parameters. So if includeStatus1 = 1 then display the rows where status = 1 and the same for the other statuses?

EDIT:

if includeStatus2 = 1(true) then retrieve all rows where status = 2.

if includeStatus3 = 1(true) then retireve all rows where status = 3

if includeStatus2 = 0(false) then dont retrieve rows where status = 2

etc

Thanks in advance.

Answer 1

Try this:

AND product.status1 = COALESCE(NULLIF(@includeStatus1, 0), product.status1)

If @includeStatus1 is null or 0, it will effectively be ignored. If it's 1, then it will filter the results.

Answer 2

Assuming 0 is not a valid status, adding this code to your WHERE clause should work. If 0 is a valid status, then either pass in NULL for your parameters instead of 0 or convert the 0's to NULL prior to the SELECT and this would still work.

and intStatus in (@includeStatus1*1, @includeStatus2*2, @includeStatus3*3, @includeStatus4*4)