Third Option for Bit Flag

I have a stored procedure similar to this, which returns rows based on whether the IsNewCustomer Bit is set to true or false (hard coded in this sample but called with parameters from a VB app in the original).

VB Code:

DECLARE @StartDate DateTime, @EndDate DateTime, @IsNewCust bit; 
SET @StartDate = '2007-09-01 00:00:00'
SET @EndDate = '2007-09-30 23:59:59'
SET @IsNewCust = 0
 
SELECT * FROM Tickets
WHERE     
    (Tickets.SaleDate BETWEEN @StartDate AND @EndDate) AND 
    (Tickets.IsNewCustomer = @IsNewCust)

Right now when I run this procedure I have to pass the IsNewCust parameter to specify which records I want back, which works great but I also want a third option to get all the records (both 0's and 1'’s).

How can that be done when the stored procedure is always looking for the parameters it contains. Is there a way to set optional parameters in SQL?

By the way this is SQL Server 2005 if that makes a difference.

Optional parameter in the example below is @param3 with a default of 0:

Code:

---

create procedure MyProc @param1 int, @param2 int, @param3 bit=0
as

---

Back to your main question. I suggest you specify the @IsNewCust param with default value of NULL. In the sproc body you do an IF test for @IsNewCust IS NULL and execute without the IsNewCustomer filter

Code:

---

IF @IsNewCust IS NULL
BEGIN
    SELECT * FROM TicketsWHERE        (Tickets.SaleDate BETWEEN @StartDate AND @EndDate)
END
ELSE
BEGIN
    SELECT * FROM TicketsWHERE        (Tickets.SaleDate BETWEEN @StartDate AND @EndDate) AND    (Tickets.IsNewCustomer = @IsNewCust)
END

---

or...

Use some bit-logic

1 - new customer
2 - not a new customer
3 - both new and not new customers

Code:

---

(Tickets.IsNewCustomer = 0 and (@IsNewCust & 1)<>0)
or (Tickets.IsNewCustomer <> 0 and (@IsNewCust & 2)<>0)

---

I never liked those bit type fields myself - why are you using them??