Third Option for Bit Flag

[FastEddie]

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).

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.

[kaffenils]

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

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

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

[szlamany]

or...

Use some bit-logic

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

(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??