Help with a where clause | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help with a where clause

I have stored procedure with three arguments:
StartDate, EndDate, TransactionType. All three are optional, thus I
gave them a default value of NULL, NULL and -1 respectively. Below is the where clause that I came up with . It checks either for valid data or
the default value. …
WHERE ((HL.ActivityDate >= @StartDate AND HL.ActivityDate <= @EndDate ) OR
(@StartDate IS NULL AND @EndDate IS NULL)) AND
((HL.DataType = @TransactionType) OR (@TransactionType = -1))
… This works fine and results come up as expected. Looking for a critique or suggestions as to make this perform better. Thanks,
Rodney

This may not be faster, but is easier to read….. WHERE HL.ActivityDate >= isnull(@StartDate,hl.activitydate)
AND HL.ActivityDate <= isnull(@EndDate,hl.activitydate)
AND((HL.DataType = @TransactionType) OR (@TransactionType = -1))
You’re missing the case when either @StartDate or @EndDate is NULL – assuming that that would be a valid call to the procedure. So instead of …
WHERE ((HL.ActivityDate >= @StartDate AND HL.ActivityDate <= @EndDate ) OR
(@StartDate IS NULL AND @EndDate IS NULL))
……………….. … I’d use this:
WHERE
((HL.ActivityDate >= @StartDate AND HL.ActivityDate <= @EndDate )
OR (HL.ActivityDate >= @StartDate AND @EndDate IS NULL)
OR (HL.ActivityDate <= @EndDate AND @StartDate IS NULL)
OR (@StartDate IS NULL AND @EndDate IS NULL))
………………..

]]>