ERROR 1064 (42000): You have an error in your SQL syntax;

Question

I am getting the following error at command line when creating this new sproc and I can't seem to figure out what's causing it. All column names have been verified in the table along with their data types to be correct.

Error:

ERROR 1064 (42000): 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 'CREATE PROCEDURE sproc_CreateFullRewardCardPass (
IN MerchantNo INT (11), 
IN Me' at line 2

here is the script:

DELIMITER //
DROP PROCEDURE IF EXISTS sproc_CreateFullRewardCardPass
CREATE PROCEDURE sproc_CreateFullRewardCardPass (
IN MerchantNo INT (11), 
IN MerchantStoreName VARCHAR (200), 
IN RewardCardType VARCHAR (20), 
IN RewardCardId BIGINT (20),
IN RewardCardTitle VARCHAR (50), 
IN RewardCardDescription VARCHAR (100), 
IN MerchantCharity VARCHAR (200), 
IN MerchantDonation DOUBLE, 
IN RewardCardExpDate DATE, 
IN RewardCardreateDate DATE, 
IN RewardCardStatus INT (1),
IN RewardCardPassHeader VARCHAR (50) 
);

BEGIN


  DECLARE RCpasstypeidenitifier TEXT;
  DECLARE RCpassformatversion INT (2);
  DECLARE RCpassteamidentifier VARCHAR (30);
  DECLARE RCpassserialnumber VARCHAR (100);
  DECLARE RCpassbackgroundcolor VARCHAR (16);
  DECLARE RCpassvisual_1xicon TEXT;
  DECLARE RCpassvisual_2xicon TEXT;
  DECLARE RCpassvisual_logo TEXT;
  DECLARE RCpasssecondary_label1 VARCHAR (50);
  DECLARE RCpasssecondary_value1 VARCHAR (50);
  DECLARE RCpassaux_label1 VARCHAR (50);
  DECLARE RCpassbarcode_type VARCHAR (20);
  DECLARE RCpassbarcode_encoding VARCHAR (200);
  DECLARE RCpassback_value1 TEXT;
  DECLARE RewardCardTitle TEXT;

  SET RCpasssecondary_value1 = 'expires';
  SET RCpassbarcode_type = 'PKBarcodeFormatQR';
  SET RCpassbarcode_encoding = 'iso-8859-1'; 
  SET RCpassback_value1 = 'RCpassback_value1';


/* CREATE FULL REWARD CARD PASS */
INSERT INTO `mcp_passes_merchants` (
passtype, 
`status`, 
merchantid, 
rewardid, 
merchantname, 
meta_description, 
passtypeidentifier, 
formatversion, 
teamidentifier, 
serialnumber, 
backgroundcolor, 
visual_1xicon, 
visual_2xicon, 
visual_logo, 
visual_text, 
meta_expdate, 
primary_label, 
primary_value, 
secondary_label1, 
secondary_value1, 
aux_label1, 
aux_value1, 
barcode_value, 
barcode_text, 
barcode_type, 
barcode_encoding, 
datecreated, 
nonprofit, 
donation, 
back_label1, 
back_value1);

VALUES (
RewardCardType, 
RewardCardStatus, 
MerchantNo,
RewardCardId,
MerchantStoreName, 
RewardCardTitle, 
RCpasstypeidenitifier, 
RCpassformatversion, 
RCpassteamidentifier, 
RCpassserialnumber, 
RCpassbackgroundcolor, 
RCpassvisual_1xicon, 
RCpassvisual_2xicon, 
RCpassvisual_logo, 
MerchantStoreName, 
RewardCardExpDate, 
RewardCardPassHeader, 
RewardCardTitle, 
RCpasssecondary_label1, 
RCpasssecondary_value1, 
RCpassaux_label1, 
RewardCardId, 
RewardCardId, 
RewardCardId, 
RCpassbarcode_type, 
RCpassbarcode_encoding, 
RewardCardCreateDate, 
MerchantCharity, 
MerchantDonation, 
RewardCardDescription,
RCpassback_value1);

END//
DELIMITER ;

Answer 1

here is how I fixed it. It turns out I had extra ';' after CREATE PROCEDURE statement.

DELIMITER $$
DROP PROCEDURE IF EXISTS sproc_CreateFullRewardCardPass $$
CREATE PROCEDURE sproc_CreateFullRewardCardPass (IN MerchantNo INT (11),IN MerchantStoreName VARCHAR (200), IN RewardCardId BIGINT (20), IN RewardCardTitle VARCHAR (50), IN RewardCardDescription VARCHAR (100), IN MerchantCharity VARCHAR (200), IN MerchantDonation DOUBLE, IN RewardCardExpDate DATE, IN RewardCardreateDate DATE, IN RewardCardStatus INT (1),IN RewardCardPassHeader VARCHAR (50) ) 
BEGIN 
  DECLARE RCpasstypeidenitifier TEXT; 
  DECLARE RCpassformatversion INT (2);
  DECLARE RCpassteamidentifier VARCHAR (30);
  DECLARE RCpassserialnumber VARCHAR (100);
  DECLARE RCpassbackgroundcolor VARCHAR (16); 
  DECLARE RCpassvisual_1xicon TEXT; 
  DECLARE RCpassvisual_2xicon TEXT; 
  DECLARE RCpassvisual_logo TEXT; 
  DECLARE RCpasssecondary_label1 VARCHAR (50); 
  DECLARE RCpasssecondary_value1 VARCHAR (50) default 'expires' ; 
  DECLARE RCpassaux_label1 VARCHAR (50); 
  DECLARE RCpassbarcode_type VARCHAR (20) default 'PKBarcodeFormatQR' ; 
  DECLARE RCpassbarcode_encoding VARCHAR (200) default 'iso-8859-1' ; 
  DECLARE RCpassback_value1 TEXT default 'RCpassback_value1' ; 
  DECLARE RewardCardType VARCHAR (20) default 'full' ; 

/* CREATE FULL REWARD CARD PASS */
INSERT INTO `tablename` (passtype, `status`, merchantid, rewardid, merchantname, meta_description, passtypeidentifier, formatversion, 
teamidentifier, serialnumber, backgroundcolor, visual_1xicon, visual_2xicon, visual_logo, visual_text, meta_expdate, primary_label, primary_value, 
secondary_label1, secondary_value1, aux_label1, aux_value1, barcode_value, barcode_text, barcode_type, barcode_encoding, datecreated, nonprofit, 
donation, back_label1, back_value1) VALUES (RewardCardType, RewardCardStatus, MerchantNo, RewardCardId, MerchantStoreName, RewardCardTitle, RCpasstypeidenitifier, RCpassformatversion, RCpassteamidentifier, RCpassserialnumber, RCpassbackgroundcolor, RCpassvisual_1xicon, RCpassvisual_2xicon, RCpassvisual_logo, MerchantStoreName, RewardCardExpDate, RewardCardPassHeader, RewardCardTitle,
RCpasssecondary_label1, RCpasssecondary_value1, RCpassaux_label1, RewardCardId, RewardCardId, RewardCardId, RCpassbarcode_type, 
RCpassbarcode_encoding, RewardCardCreateDate, MerchantCharity, MerchantDonation, RewardCardDescription, RCpassback_value1);
END $$
DELIMITER ;;

Answer 2

You need to end every statement with a delimiter. In your case it is //. Add this after the drop statement

DROP PROCEDURE IF EXISTS sproc_CreateFullRewardCardPass //
                          here -------------------------^