user123456789 user123456789 - 6 months ago 14
MySQL Question

Using two IF THEN in sql stored procedure

I am trying to create a stored procedure in MySQL Workbench.
I need to check if a certain flag is equal to 1. If the flag is equal to 1, then search by

FollowupDate
else use
QuoteDate


IF(qFollowup = 1) THEN
IF(pFromFollowupDate != '') THEN
BEGIN
SET @Main = CONCAT(@Main, ' AND DATE(q.FollowupDate) >= \'',pFromFollowupDate,'\' ');
END;
ELSE
IF(pFromDate != '') THEN
BEGIN
SET @Main = CONCAT(@Main, ' AND DATE(q.QuoteDate) >= \'',pFromDate,'\' ');
END;
END IF;
END IF;


When I try to apply the changes it says there are syntax errors.

Answer

Try:

DELIMITER //

CREATE PROCEDURE `sp_name`()
BEGIN
  IF qFollowup = 1 THEN
    IF pFromFollowupDate != '' THEN
      SET @Main = CONCAT(@Main, '  AND DATE(q.FollowupDate)  >= \'',pFromFollowupDate,'\' ');
    ELSE
      IF pFromDate != '' THEN
        SET @Main = CONCAT(@Main, '  AND DATE(q.QuoteDate)  >= \'',pFromDate,'\' ');
      END IF;
    END IF;
  END IF;
END//

DELIMITER ;