MySQL Forums
Forum List  »  Stored Procedures

Re: Stored procedure with variable table name
Posted by: Bob Barker
Date: May 03, 2017 03:31PM

I've changed the sproc to the following

CREATE PROCEDURE sp_GetRecordID (
IN formSecret_In varchar(50),
IN tablename_In varchar(200),
OUT id_Out INT(11)
)
BEGIN
SET @getID = CONCAT(‘SELECT id as id_Out FROM ‘,tablename_In,’ WHERE formSecret=',formSecret_In);
PREPARE stmt FROM @getID;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;

When I run it against a simple number, it works. If I make that a string, I get an error. For example:

CALL sp_GetRecordID(123456,'tbl_JustSayThanks',@id_Out) will return the correct record.

However, when I do
CALL sp_GetRecordID('fc66d9a82ba717e0931462370e64baff','tbl_JustSayThanks',@id_Out), I get an error message - "#1054 - Unknown column 'fc66d9a82ba717e0931462370e64baff' in 'where clause" - which makes no sense to me as the numeric version worked and I can't see where the breakdown is.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored procedure with variable table name
2709
May 03, 2017 03:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.