creating mysql update stored procedure

Question

am getting error 1064 creating an update stored procedure, i dont know where am wrong

DELIMITER $$

CREATE

PROCEDURE `sms_pigen`.`sp_update`(IN Clearing_Agent_ID INT(11), IN ClientCode VARCHAR(10), IN ClientName VARCHAR(50), IN PostalAdd VARCHAR(200), IN Telephone VARCHAR(30), IN Fax VARCHAR (30), IN EmailAdd1 VARCHAR(255), IN EmailAdd2 VARCHAR(255), IN EmailAdd3 VARCHAR(255), IN Website VARCHAR(50), IN TotalDeposit DECIMAL(18,2), IN AccountBal DECIMAL(18,2), IN ChargeRate DECIMAL(10,2))

BEGIN
UPDATE t_pi_clients SET pClientCode = ClientCode, pClientName = ClientName, pPostalAdd = PostalAdd, pTelephone = Telephone, pFax = Fax, pEmailAdd1 = EmailAdd1, pEmailAdd2 = EmailAdd2, pEmailAdd3 = EmailAdd3, pWebsite = Website, pTotalDeposit = TotalDeposit, pAccountBal = AccountBal, pChargeRate = ChargeRate WHERE Clearing_Agent_ID = Clearing_Agent_ID;

END$$

DELIMITER ;

Error code

Query : CREATE     /*[DEFINER = { user | CURRENT_USER }]*/     PROCEDURE `sms_pigen`.`sp_update`(IN Clearing_Agent_ID INT(11), IN Client...

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 '/*LANGUAGE SQL /*LANGUAGE SQL END$$ DELIMITER' at line 16 Execution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000

What are you using to run this create code as it looks like it is injecting some extra text? — Giles
looks like my end$$ was somehow commented,so problem solved ty — RichieCr7

Prince Raj Prince Raj · Accepted Answer · 2021-01-29T08:54:35

CREATE DEFINER=`pcbb-uat`@`%` PROCEDURE `Update_Data`(IN TABLE_NAME longtext,in column_name varchar(1000),in column_value longtext,in Parameter varchar(1000),in parameter_value varchar(100))
BEGIN
     
                set @whereclouse = CONCAT('[',Parameter,'] = ''',parameter_value,'''');
                SET @query = CONCAT('update [',TABLE_NAME,'] SET [',column_name,'] = ''',column_value,'''  WHERE ');
                set @updt = concat(@query,@whereclouse);
                select @updt;
                PREPARE stmt FROM @updt;
                EXECUTE stmt ;
                DEALLOCATE PREPARE stmt;
END

creating mysql update stored procedure

1 Answers