SQL Server Performance Forum – Threads Archive

Filtering Question — Probably easy!

Hello, I have a question on the best way to do a particular SQL query. This is the basic idea of what I want to do: <pre id="code"> DECLARE @Status INT DECLARE @ExcludeStatus INT SET @Status = Null SET @ExcludeStatus = 5 SELECT r.RecordNumber, r.Status FROM Records r WHERE ISNULL(r.Status, ”) = COALESCE(@Status, r.Status, ”) AND ISNULL(r.Status, ”) != COALESCE(@ExcludeStatus, ”, ”) </pre id="code"> The above query works, but I would like to avoid using the @ExcludeStatus parameter. Basically, I just want to exclude status 5 by default, unless it’s specifically requested in the @Status parameter. So, I would like to be able to submit null for @Status, and get only status 1, 2, 3 and 4. I would like to be able to specify a status 1-5 and get only that status (including 5). This is probably easy for must of you… I’m not a DBA <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />

Hi ya, I’d use an if condition for this… DECLARE @Status INT
DECLARE @ExcludeStatus INT
SET @Status = Null
SET @ExcludeStatus = 5 if @Status is null
SELECT r.RecordNumber,
r.Status
FROM Records r
WHERE r.Status in ( 1, 2, 3, 4 )
else
SELECT r.RecordNumber,
r.Status
FROM Records r
WHERE r.Status = @Status Cheers
Twan

]]>