Re: Insert into dynamic tableName

[John Noble]

Hi Peter,

I think I have solved it.

Here is the complete sproc...

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(
IN p_credit varchar(1),
IN p_reference1 varchar(25),
IN p_vesselID varchar(7),
IN p_period varchar(4)
)
BEGIN

-- DECLARE vLedgerYear varchar(20) default "";
DECLARE vInsert varchar(255) default "";

SET @vLedgerYear = CONCAT("ledger", substring(p_period, 1, 2));


START TRANSACTION;


SET @vInsert = CONCAT("INSERT INTO ", @vLedgerYear, " (credit, reference1, vesselID)
VALUES ('", p_credit, "','" , p_reference1, "','" , p_vesselID, "')");

PREPARE stmt FROM @vInsert;
EXECUTE stmt;

COMMIT;



I removed the DECLARE statement I had previously and changed
SET @vLedgerYear = "ledger" + substring(p_period, 1, 2);
to
SET @vLedgerYear = CONCAT("ledger", substring(p_period, 1, 2));

Now it is working.

Thanks again for your help. Much appreciated as always.

J