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: stackoverflow.com/questions/25414333/… — DaveShaw
– DaveShaw, Commented Aug 21, 2014 at 21:45

D Stanley · Accepted Answer · 2014-08-21 21:46:41Z

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.

and what is code would be in /*ALL*/ and /* match case*/ bracket

Tom Page · Accepted Answer · 2014-08-21 21:50:01Z

2

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

Excludes no records if @Status is null

answered Aug 21, 2014 at 21:50

Tom Page

1,2511 gold badge7 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL where if statement

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related