CODEWITHSUNDEEP
sqlsql-server
Wompguinea
Wompguinea

Reputation: 378

Range of values as ELSE clause in a CASE statement

I have a stored proc which is supposed to filter results based on an integer which can be NULL and I need to be able to choose results that either match the integer or retrieve all results but I can't figure out the syntax for the "All" results part. Query is basically this:

Select * from dbo.TABLE 
WHERE [IDField] = 
  CASE 
    WHEN ISNULL(@ID, 0) > 0 THEN @ID 
    ELSE ????? 
  END

I want to use an IN range to include all the values because setting it to 0 or Null will return no results, but this doesn't work:

Select * from dbo.TABLE 
WHERE [IDField] = 
  CASE 
    WHEN ISNULL(@ID, 0) > 0 THEN @ID 
    ELSE 1 OR 2 OR 3 
  END

or this:

Select * from dbo.TABLE 
WHERE [IDField] IN (
  CASE 
    WHEN ISNULL(@mID, 0) > 0 THEN @mID 
    ELSE 1,2,3 
  END
)

Upvotes: 0

Views: 248

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Phrase this without the case:

SELECT * 
FROM dbo.TABLE 
WHERE @id is null OR IDField = @id;

Then you can expand this to:

SELECT * 
FROM dbo.TABLE 
WHERE (COALESCE(@ID, 0) > 0 AND IDField = @id) OR
      IDField IN (1, 2, 3)

Upvotes: 1

Related Questions