2

Hi I tried implementing the below stored procedure which always takes my if condition as true and implements .

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID varchar(20))
BEGIN
    if(SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID = 0) THEN

        UPDATE `smart_scheduler1`.`work_schedule` SET `Employee_ID`=uid WHERE `Schedule_ID`=S_ID;
        ELSE
        SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID;

END IF;
END

My question is how do I compare a query with a value or integer in stored procedure if condition?

I am novice in Database , please help me. I would be very pleased if you could get me a good tutorial on stored procedure as I was not able to find much materials on stored procedures online.

  • Is there any particular reason for saying COUNT(SLOT_ID) instead of COUNT(*)? – Rick James Mar 31 '16 at 0:15
0

Your if condition should actually almost always be false, unless you have an entry where schedule_id = 0.

It may be surprising, but in MySQL you can write something like this:

root@localhost:(none) > select 1=1=1;
+-------+
| 1=1=1 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

root@localhost:(none) > select 1=1=2;
+-------+
| 1=1=2 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

So in your case you actually don't compare anything in your if condition, there is just a query which returns a number. And the if condition is true whenever it's not 0.

The solution here is to use parantheses.

IF((SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID = uid AND Schedule_ID = S_ID) = 0) THEN

Or you could do

IF NOT EXISTS(SELECT Slot_ID FROM smart_scheduler1.work_schedule where Employee_ID = uid AND Schedule_ID = S_ID) THEN

I personally like to use (NOT) EXISTS very much, not only because it's so easily readable, but EXISTS stops as soon as an entry is found and doesn't continue to search till the very end, although the exact number is of no interest.

If the exact number matters, you can of course also use variables like this:

SELECT @my_variable := COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID
IF(@my_variable = 0) THEN

or

DECLARE my_variable INT;
SELECT COUNT(Slot_ID) INTO my_variable FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID
IF(my_variable = 0) THEN

You can read about the differences of these two kinds of variables here and here.

  • thanks a lot , not sure why but it did not work .. the ELSE statement executes only when there is data already present in the table . or else it just updates without checking if there is any repeated values in slot_ID – avinash v p Mar 30 '16 at 8:19
  • What did you do exactly? With a simple "it did not work" I can not help you. – tombom Mar 30 '16 at 8:20
  • it just executes skipping the if not exist or if condition .. tried all the above statements – avinash v p Mar 30 '16 at 8:24
  • How do you know, this is not correct behaviour? Have you checked your data? – tombom Mar 30 '16 at 8:29
  • Guess I shuld do the same , i created a simple table which implemented the query , but was not able to implement the query in my database. – avinash v p Mar 30 '16 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.