What you need is Dynamic SQL via the PREPARE statement
Like This :
CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20))
BEGIN
DECLARE SQLCommand VARCHAR(1024);
SET SQLCommand = CONCAT('SELECT * FROM ',myTable);
SET @SQLStmt = SQLCommand;
PREPARE s1 FROM @SQLStmt;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END $$
Give it a Try !!!
-----Original Message-----
From: roger.maynard [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 23, 2008 6:44 AM
To: [email protected]
Subject: Pass Reference to source table in Stored Procedure - How??
Hi
Is there any way I can pass the reference to a source table to be used
in a SELECT command within a Stored Procedure
Something like this....
CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20))
BEGIN
SELECT * FROM myTable;
END $$
This gives error "cannot find Table myDB.myTable
I cant find how to pass reference in this context. If its not possible,
are there any suggestions as to how to create a flexible SP whereby I
can pass the source?
I can do it by repeating the main select within a switch case structure
but it doesn't seem a very elegant solution
Thanks for looking
Roger Maynard
Somerset UK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
