This is how I written a stored procedure in mysql version 6.0.11-alpha-community using if (if else) elseif (if else) statements.
Tables are :
First Table
CREATE TABLE `tbl_smsnotifications` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UID` varchar(20) NOT NULL,
`SmsIntradayPicks` varchar(10) DEFAULT 'False',
`SmsAlerts` varchar(10) DEFAULT 'False',
`SmsStrategyPicks` varchar(10) DEFAULT 'False',
PRIMARY KEY (`UID`),
KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
And second table
CREATE TABLE `tbl_webnotifications` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UID` varchar(20) NOT NULL,
`WebIntradayPicks` varchar(10) DEFAULT 'False',
`WebAlerts` varchar(10) DEFAULT 'False',
`WebStrategyPicks` varchar(10) DEFAULT 'False',
PRIMARY KEY (`UID`),
KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SP Query is :
DELIMITER $$
USE `marketprice_nse`$$
DROP PROCEDURE IF EXISTS `webandsmsnotificationsinsert`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `webandsmsnotificationsinsert`(IN WSNUid VARCHAR(20), IN WSNColType VARCHAR(20),
IN WSCategory VARCHAR(10),IN WSNOptType VARCHAR(20))
BEGIN
IF (WSNUid != '' && WSCategory = 'WEB' )
THEN
IF (SELECT COUNT(*) FROM tbl_webnotifications WHERE uid = wsnuid ) = 0
-- if not exists (select 1 from tbl_webnotifications WHERE uid = WSNUid)
THEN
IF (WSNOptType = 'WebIntradayPicks')
THEN
INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
VALUES (WSNUid,WSNColType,'False','False');
ELSEIF (WSNOptType = 'WebAlerts')
THEN
INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
VALUES (WSNUid,'False',WSNColType,'False');
ELSEIF (WSNOptType = 'WebStrategyPicks')
THEN
INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
VALUES (WSNUid,'False','False',WSNColType);
END IF;
END IF;
END ;
ELSE
BEGIN
-- then
IF (WSNOptType = 'WebIntradayPicks')
THEN
UPDATE tbl_webnotifications SET WebIntradayPicks = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'WebAlerts')
THEN
UPDATE tbl_webnotifications SET WebAlerts = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'WebStrategyPicks' && WSCategory = 'WEB')
THEN
UPDATE tbl_webnotifications SET WebStrategyPicks = WSNColType WHERE UID = WSNUid;
END IF;
END;
ELSEIF (WSNUid != '' && WSCategory = 'SMS')
THEN
IF (SELECT COUNT(*) FROM tbl_smsnotifications WHERE uid = wsnuid ) = 0
-- IF NOT EXISTS (SELECT 1 FROM tbl_smsnotifications WHERE uid = WSNUid)
THEN
IF ( WSNOptType = 'SmsIntradayPicks')
THEN
INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
VALUES (WSNUid,WSNColType,'False','False');
ELSEIF (WSNOptType = 'SmsAlerts')
THEN
INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
VALUES (WSNUid,'False',WSNColType,'False');
ELSEIF (WSNOptType = 'SmsStrategyPicks')
THEN
INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
VALUES (WSNUid,'False','False',WSNColType);
END IF;
END IF;
END;
ELSE
-- THEN
IF (WSNOptType = 'SmsIntradayPicks')
THEN
UPDATE tbl_smsnotifications SET SmsIntradayPicks = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'SmsAlerts')
THEN
UPDATE tbl_smsnotifications SET SmsAlerts = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'SmsStrategyPicks' && WSCategory = 'WEB')
THEN
UPDATE tbl_smsnotifications SET SmsStrategyPicks = WSNColType WHERE UID = WSNUid;
END IF;
END;
END IF;
END;
END$$
DELIMITER ;
While executing the above SP I'm getting this error
0 row(s) affected
Execution Time : 0 sec
Transfer Time : 0.001 sec
Total Time : 0.001 sec
---------------------------------------------------
0 row(s) affected, 1 warning(s)
Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0 sec
Note Code : 1305
PROCEDURE marketprice_nse.webandsmsnotificationsinsert does not exist
---------------------------------------------------
Query: CREATE DEFINER=`root`@`localhost` PROCEDURE `webandsmsnotificationsinsert`(in WSNUid varchar(20), IN WSNColType VARCHAR(20), in ...
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
else
begin
-- then
IF (WSNOptType = 'WebIntradayPicks')
' at line 31
Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0 sec
---------------------------------------------------
Note : the column names are same as I mentioned in SP
I think I'm not used properly the "if (if else) elseif (if else) statements." statement
How can I achieve this?
(I'm new to MYSQL DB)
You have some errors in your procedure.
END should be on the end of the sp and you have two BEGIN.
Somehow I made it working but I do not know if it is what you are looking for. Check it and tell me if it helps ?
DELIMITER $$
CREATE PROCEDURE `webandsmsnotificationsinsert`(IN WSNUid VARCHAR(20), IN WSNColType VARCHAR(20),
IN WSCategory VARCHAR(10),IN WSNOptType VARCHAR(20))
BEGIN
IF (WSNUid != '' && WSCategory = 'WEB' )
THEN
IF (SELECT COUNT(*) FROM tbl_webnotifications WHERE uid = wsnuid ) = 0
THEN
IF (WSNOptType = 'WebIntradayPicks')
THEN
INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
VALUES (WSNUid,WSNColType,'False','False');
ELSEIF (WSNOptType = 'WebAlerts')
THEN
INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
VALUES (WSNUid,'False',WSNColType,'False');
ELSEIF (WSNOptType = 'WebStrategyPicks')
THEN
INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
VALUES (WSNUid,'False','False',WSNColType);
END IF;
END IF;
IF (WSNOptType = 'WebIntradayPicks')
THEN
UPDATE tbl_webnotifications SET WebIntradayPicks = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'WebAlerts')
THEN
UPDATE tbl_webnotifications SET WebAlerts = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'WebStrategyPicks' && WSCategory = 'WEB')
THEN
UPDATE tbl_webnotifications SET WebStrategyPicks = WSNColType WHERE UID = WSNUid;
END IF;
ELSEIF (WSNUid != '' && WSCategory = 'SMS')
THEN
IF (SELECT COUNT(*) FROM tbl_smsnotifications WHERE uid = wsnuid ) = 0
-- IF NOT EXISTS (SELECT 1 FROM tbl_smsnotifications WHERE uid = WSNUid)
THEN
IF ( WSNOptType = 'SmsIntradayPicks')
THEN
INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
VALUES (WSNUid,WSNColType,'False','False');
ELSEIF (WSNOptType = 'SmsAlerts')
THEN
INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
VALUES (WSNUid,'False',WSNColType,'False');
ELSEIF (WSNOptType = 'SmsStrategyPicks')
THEN
INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
VALUES (WSNUid,'False','False',WSNColType);
END IF;
END IF;
IF (WSNOptType = 'SmsIntradayPicks')
THEN
UPDATE tbl_smsnotifications SET SmsIntradayPicks = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'SmsAlerts')
THEN
UPDATE tbl_smsnotifications SET SmsAlerts = WSNColType WHERE UID = WSNUid;
ELSEIF (WSNOptType = 'SmsStrategyPicks' && WSCategory = 'WEB')
THEN
UPDATE tbl_smsnotifications SET SmsStrategyPicks = WSNColType WHERE UID = WSNUid;
END IF;
END IF;
END$$
DELIMITER ;