MySQL Forums
Forum List  »  Stored Procedures

Stored procedure with variable table name
Posted by: Bob Barker
Date: May 02, 2017 09:23AM

I'm trying to create a stored procedure that lets me pass in a table name and return the id of a specific record.

I've got a sproc to work - or at least not error but it isn't returning any data and I can't see why. In the sproc, I'm passing in the name of the table and a randomly generated string. I do a select on the table to see if there are any records matching the string and, if so, I was to report out the id of the query.

What I have so far is:

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

Running it in PhpMyAdmin, I get:
SET @p0='bcbf491ec294adcdeb89d15e8d31f5fa'; SET @p1='tbl_JustSayThanks'; CALL `sp_GetRecordID`(@p0, @p1);

I don't get any errors or results. I should get something as there is definitely a record to correspond to that string.

While I have experience in MSSQL, I'm still struggling to make the switch to MySQL, so any help would be greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored procedure with variable table name
551
May 02, 2017 09:23AM


Sorry, only registered users may post in this forum.

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.