September 15, 2004 at 11:12 am
How do I use a parameters value in the WHERE clause to
1. Return all records if the parameter value IS NULL
2. Return all records if the parameter value is "ALL"
3. Return specified records when parameter value is neither NULL nor "ALL"
I have tried:
WHERE
(F060116.YAPAST = '0')
AND (F060116.YAEST =
CASE
WHEN @ReviewType = 'A' THEN ' '
WHEN @ReviewType = '9' THEN '1'
ELSE ' '--!
END)
AND (CASE
WHEN @EmployeeNumber = 0 THEN (1=1)
WHEN @EmployeeNumber IS NULL THEN (1=1)
ELSE (F060116.YAAN8 = @EmployeeNumber)
END)
The first CASE/END works but will not handle "ALL" or NULL values.
The second CASE/END returns an Incorrect syntax near '='. Error
THANKS for any prompt ideas for handling this situation!!!!!
September 15, 2004 at 11:33 am
Solved with the following:
AND (CASE
WHEN @EmployeeNumber IS NULL THEN F060116.YAAN8
WHEN @EmployeeNumber = 0 THEN F060116.YAAN8
ELSE @EmployeeNumber
END = F060116.YAAN8)
September 16, 2004 at 3:36 am
An example using Northwind:
declare @LastName nvarchar(20)
set @LastName = 'All'
select *
from Employees
where LastName = isnull(nullif(@LastName,'All'),LastName)
September 16, 2004 at 8:46 am
How about:
Where F060116.YAPAST = '0' AND (@ReviewType = 'A' OR @ReviewType IS NULL OR @EmployeeNumber = F060116.YAAN8)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply