IF condition in MYsql not giving proper result

Question

This is code of mysql stored procedure:

BEGIN
DECLARE sms INT(100);
SET sms=(SELECT COUNT(1) FROM tbl_intofone_alert_transaction WHERE smsid='sms_id');
IF(sms<=>0) THEN
INSERT INTO tbl_intofone_alert_transaction(MSISDN,Message,Tag,SenderID,InTime,deliverystatus,
memberid,smsid,updatetime,submit_type,wu_id,DeviceID) 
VALUES(mob,msg,tag,sender,NOW(),'0',memid,sms_id,NOW(),'NULL','NULL',DevId);
END IF;
   END$$

IF CONDITION in above code doing insertion for all the cases . what i have done wrong here?

Well, what happens if you just run the first `SELECT` manually? And is it possible that `sms` is ever `NULL`? — Ja͢ck, Dec 18 '12 at 14:38
@Ekansh Really? Because `smsid` doesn't sound like a column that can contain a string. — Ja͢ck, Dec 18 '12 at 14:42
Why you don't use the solution provided here: http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table It is much cleaner and does not require a variable. — CodeZombie, Dec 18 '12 at 14:44
@Jack sorry i have found what was the problem actually . (1) i have assigned variable "sms_id" as string and (2) in the "IF statement" use of operator <=> it must be <=. thanx for pointimg that.. — Ekky, Dec 18 '12 at 14:46
@Ekansh: Actually both `<=> 0` and `<= 0` will work fine, but isn't it much simpler to just use `= 0` to test for equality with zero? It seems like you are trying random operators until it works instead of thinking about how to solve the problem. — Mark Byers, Dec 18 '12 at 14:59
thank you @MarkByers , i have use "=" as per my requirement. — Ekky, Dec 18 '12 at 15:00

score 2 · Accepted Answer · answered Dec 18 '12 at 14:38

it must be

   IF(sms<=0) THEN    //sms  less then or equal 0

or

  IF(sms>=0) THEN      //sms  bigger then or equal 0

<=> this is not comparaison in mysql

obs

  VALUES(mob,msg,tag,sender,NOW(),'0',memid,sms_id,NOW(),'NULL','NULL',DevId);

most of those values i dont know what they are , they looks wrong

they must be variables that u get them from your code.

in this code there is only NOW() and NULL which are right

Mark Byers · Answer 2 · 2012-12-18T14:44:40.280

1

Are you sure that you want to search for the string literal 'sms_id' here?

SELECT COUNT(1) FROM tbl_intofone_alert_transaction WHERE smsid='sms_id'

Probably you meant to use the value of the sms_id variable:

SELECT COUNT(1) FROM tbl_intofone_alert_transaction WHERE smsid = sms_id

edited Dec 18 '12 at 14:44

answered Dec 18 '12 at 14:39

Mark Byers

719,658
164
1,497
1,412

1

This one right: SELECT COUNT(1) FROM tbl_intofone_alert_transaction WHERE smsid='42' – Ekky Dec 18 '12 at 14:42
1

Try `sms_id` (without the quotes). – Mark Byers Dec 18 '12 at 14:43

IF condition in MYsql not giving proper result

2 Answers2