Re: Escape sproc Parameters

[John Noble]

Hi Peter,

When I use the Quote() function it adds single quotes to the beginning and end of the string which is not what I want.

Here is my problem:

Works fine.
call usp_TestUpdate("MS1_", "boats", 14, "Johns Boat")

Does NOT Work (Note the ' in John's Boat)
call usp_TestUpdate("MS1_", "boats", 14, "John's Boat")


CREATE PROCEDURE `usp_TestUpdate`(
IN p_companyID varchar(4)
IN p_pKey int(10),
IN p_name varchar(20)

)
BEGIN


DECLARE vUPDATE varchar(1000) default "";
DECLARE vTableName varchar(9) default "";


SET @vTableName = CONCAT(p_companyID, "boats");


START TRANSACTION;

SET @vUPDATE = CONCAT("UPDATE ", @vTableName," SET name = '", p_name, "'", " WHERE pKey = ", p_pKey);

EXECUTE stmt;
COMMIT;


END