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.
Re: Third Option for Bit Flag
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
Re: Third Option for Bit Flag
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??