If, Else if, Else not working Only if and Else Working

Question

I have the following query:

Declare @qty int
SET @qty = (SELECT Qty FROM StockTB WHERE ProductID='6' AND Qty=0)
if(@qty =0)
Update StockTB SET Qty=Qty+5, BatchNo='1234', ExpDate='03/11/2018' WHERE 
ProductID='6' AND Qty=0
Else
INSERT INTO StockTB Values('6', '5', '1234', '03/11/2018')

The above query is working fine. But when I add ELSE IF in the query, it fails. Here is after having ELSE IF implementation.

Declare @expdate date
SET @expdate =(SELECT ExpDate FROM StockTB WHERE ProductID='6' AND 
ExpDate='03/11/2018')
Declare @qty int
SET @qty = (SELECT Qty FROM StockTB WHERE ProductID='6' AND Qty=0)
if(@qty =0)
Update StockTB SET Qty=Qty+5, BatchNo='1234', ExpDate='03/11/2018' WHERE 
ProductID='6' AND Qty=0
Else if(@expdate='03/11/2018')
Update StockTB SET Qty=Qty+5 WHERE ProductID='6' AND ExpDate='03/11/2018'
Else
INSERT INTO StockTB Values('6', '5', '1234', '03/11/2018')

SET @qty = (SELECT Qty FROM StockTB WHERE ProductID='6' AND Qty=0) - when do you expect that to be non-zero? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 18, 2017 at 12:44
@Damien_The_Unbeliever There's one case: if no result (NULL) is returned by the query. — MatSnow
– MatSnow, Commented Oct 18, 2017 at 12:54
@MatSnow - agreed - this would be a very bizarre way of writing what appears to just need to be a single MERGE. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 18, 2017 at 12:55
What exactly do you mean " Failed to work"? What do you expect and what do you get? — HoneyBadger
– HoneyBadger, Commented Oct 18, 2017 at 12:56
You should read about MERGE. learn.microsoft.com/en-us/sql/t-sql/statements/… It is a lot easier way to handle this kind of thing. — Sean Lange
– Sean Lange, Commented Oct 18, 2017 at 13:09

Bruno Miquelin · Accepted Answer · 2017-10-18 13:35:14Z

2

I can't read your schema, but it seems that "ExpDate" is a date. You are comparing it to a varchar '03/11/2018'

I don't know if you mean November 3 or March 11. Neither SQL do know. Try to CONVERT(VARCHAR, ExpDate, 103) instead:

At: Else if(@expdate='03/11/2018')

Change it to: Else if(CONVERT(VARCHAR, @expdate, 103) = '03/11/2018')

You can also achieve the same result using that on the query to set the @expdate value (And make it VARCHAR, not date)

*Ps: The magic number 103 as argument means dd/mm/yyyy. You can get a full list here.

edited Oct 18, 2017 at 13:35

answered Oct 18, 2017 at 12:52

Bruno Miquelin

6611 gold badge8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Lange Over a year ago

Or use the ANSI standard YYYYMMDD.

Arun Udumbassery · Accepted Answer · 2017-10-18 13:20:37Z

0

Please try this

Declare @expdate date
SET @expdate =(SELECT ExpDate FROM StockTB WHERE ProductID='6' AND 
ExpDate='2018/11/03')
Declare @qty int
SET @qty = (SELECT Qty FROM StockTB WHERE ProductID='6' AND Qty=0)
if(@qty =0)
Update StockTB SET Qty=Qty+5, BatchNo='1234', ExpDate='2018/11/03' WHERE 
ProductID='6' AND Qty=0
Else 
if(@expdate='2018/11/03')
Update StockTB SET Qty=Qty+5 WHERE ProductID='6' AND ExpDate='2018/11/03'
Else
INSERT INTO StockTB Values('6', '5', '1234', '2018/11/03')

edited Oct 18, 2017 at 13:20

answered Oct 18, 2017 at 13:03

Arun Udumbassery

197 bronze badges

1 Comment

Sean Lange Over a year ago

This isn't any different than what the OP posted.

Collectives™ on Stack Overflow

If, Else if, Else not working Only if and Else Working

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related