I have a table with design
CREATE TABLE IF NOT EXISTS InsuranceContract (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`enquiryCode` VARCHAR(20) DEFAULT NULL,
`contractCode` VARCHAR(20) DEFAULT NULL,
`createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP (),
`updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP () ON UPDATE CURRENT_TIMESTAMP (),
UNIQUE KEY (`enquiryCode`)) ENGINE=INNODB DEFAULT CHARSET=UTF8 COLLATE = UTF8_BIN;
Then I was created a procedure like this
DROP procedure IF EXISTS `sp_insurance_contract_get`;
DELIMITER $$
CREATE PROCEDURE `sp_insurance_contract_get` (enquiryCode VARCHAR(20), contractCode VARCHAR(20))
BEGIN
SET @t1 = "SELECT * FROM InsuranceContract
WHERE InsuranceContract.enquiryCode = enquiryCode
AND InsuranceContract.contractCode = contractCode;";
PREPARE param_stmt FROM @t1;
EXECUTE param_stmt;
DEALLOCATE PREPARE param_stmt;
END$$
DELIMITER ;
And I was executed this procedure in MySQL Workbench by this command:
CALL sp_insurance_contract_get('EQ000000000014', '3001002');
I expected I will receive 1 row result but it selected all records in this table.
If I copy and create exactly this @t1 into plain SQL not using statement, it's correct.
Please help me to fix this error. I'm using MySQL 8.0.19