SQL where if statement

Question

Have stored procedure some like this.

CREATE PROCEDURE test
  @ID
  @inventory bit = 0
  @status nvarchar(2) = null
AS
BEGIN
 SELECT 
   ID
   INVENTORY = (SELECT SUM(Inventory) FROM Option WHERE /*Inventory if 0 with if 1 without*/)
   STATUS
 WHERE 
   --TODO Status if
END

how to create if statement when status can be null, '', 'preordered' for example: if null select all status, if '' select empty status, if some status - select some status. And need to create where condition for Inventory(with/without)

Have a look at this question from the other day: http://stackoverflow.com/questions/25414333/best-way-to-do-a-case-where-clause-sql-server/25414390#25414390 — DaveShaw, Aug 21 '14 at 21:45

score 2 · Accepted Answer · answered Aug 21 '14 at 21:46

2

if null select all status, if '' select empty status, if some status - select some status

Just add a check for a null status and don't include any other condition in that case:

WHERE (@status IS NULL) /* ALL */ 
   OR
    (@status = STATUS) /* match status */

If you add a condition for Inventory make sure you put the status check in parentheses. AND gets evaluated before OR so it's best to logically group the conditions so they don't get evaluated out-of-order.

answered Aug 21 '14 at 21:46

D Stanley

149,601
11
178
240

and what is code would be in `/*ALL*/` and `/* match case*/` bracket – AleksP Aug 21 '14 at 21:52
@AleksP none - those are inline comments. – D Stanley Aug 21 '14 at 22:01

score 2 · Answer 2 · answered Aug 21 '14 at 21:50

2

WHERE (@status IS NULL  OR (@status  =STATUS))

Excludes no records if @Status is null

answered Aug 21 '14 at 21:50

Tom Page

1,211
1
7
8

SQL where if statement

2 Answers2