Can I use 2 tables in single stored procedure

Question

I am trying to write a stored procedure where I am using 2 tables to fetch data based on a condition but it is giving the error.

My SP is:

DELIMITER //
CREATE PROCEDURE select_notifications(IN datePosted DATETIME,IN recipientId INT,IN packageId INT,IN accountId INT)
BEGIN
 IF datePosted < NOW() THEN
  SELECT tn.subject_line,tn.message,tn.message_type FROM track_notifications AS tn WHERE tn.package_id=packageId AND tn.recipient_id=recipientId AND tn.account_id=accountId AND tn.date_posted = datePosted;
 ELSE IF datePosted > NOW()
  SELECT subject_line,message,message_type FROM track_notifications_new WHERE package_id=packageId AND recipient_id=recipientId AND account_id=accountId AND date_posted = datePosted;
 END IF;
END
END //

Mysql Error:

The following query has failed: "CREATE DEFINER=root@localhost PROCEDURE select_track_notifications(IN datePosted DATETIME, IN recipientId INT, IN packageId INT, IN accountId INT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN IF datePosted < NOW() THEN SELECT tn.subject_line,tn.message,tn.message_type FROM track_notifications AS tn WHERE tn.package_id=packageId AND tn.recipient_id=recipientId AND tn.account_id=accountId AND tn.date_posted = datePosted; ELSE IF datePosted > NOW() THEN SELECT subject_line,message,message_type FROM track_notifications_new WHERE package_id=packageId AND recipient_id=recipientId AND account_id=accountId AND date_posted = datePosted; END IF; END"

Answer 1

• You have an extra END at the end of your statement.
• Also, you will need to reset the Delimiter to ; at the end.
• Else if syntax is ELSEIF .... THEN (no space between ELSE and IF).
• Also, I think you may need to use SELECT .. INTO clause to store the results of your select query into variables (on which you will do further actions).

Try the following instead:

DELIMITER //
DROP PROCEDURE IF EXISTS select_notifications //
CREATE PROCEDURE select_notifications(IN datePosted DATETIME,
                                      IN recipientId INT,
                                      IN packageId INT,
                                      IN accountId INT)
BEGIN
 IF datePosted < NOW() THEN
  SELECT tn.subject_line,
         tn.message,
         tn.message_type 
  FROM track_notifications AS tn 
  WHERE tn.package_id=packageId AND 
        tn.recipient_id=recipientId AND 
        tn.account_id=accountId AND 
        tn.date_posted = datePosted;

 ELSEIF datePosted > NOW() THEN 
  SELECT subject_line,
         message,
         message_type 
  FROM track_notifications_new 
  WHERE package_id=packageId AND 
        recipient_id=recipientId AND 
        account_id=accountId AND 
        date_posted = datePosted;
 END IF;
END //

DELIMITER ;