How do I select each row from a table based on the row that contains the table name?
In MySQL, I have several procedures that are more or less identical - they all perform the same (or very similar) operations, but perform them on different tables.
I would like to reduce them to a single procedure, parameterized with the table name, if possible. For example, suppose I wanted to perform a generic selection:
SELECT * FROM TableFor("TableName")
Is this (or something similar) possible in MySQL? Is this possible in any conversational SQL language?
Reply to a message from Tomva
Complete example:
DROP PROCEDURE IF EXISTS example;
CREATE PROCEDURE example(IN tablename VARCHAR(1000)) BEGIN
SET @statement = CONCAT('SELECT * FROM ', @tablename);
PREPARE statement FROM @statement;
EXECUTE statement;
DEALLOCATE PREPARE statement;
END;
CALL example('tablename');
source to share
You can do this with a prepared statement .
It will be something like lines
SET @stat = CONCAT('SELECT * FROM ', @tab');
PREPARE stat1 FROM @stat;
EXECUTE stat1;
DEALLOCATE PREPARE stat1;
Dynamic SQL doesn't work in a function, so make a stored procedure out of it and you can specify a table parameter.
source to share
I'm going to assume that you know what a stored procedure is (I hope my answer is otherwise useless)
First create a table object in your procedure
declare @tablenames table(name varchar)
insert into @MonthsSale (name) values ('firsttable')
insert into @MonthsSale (name) values ('secondtable')
...
You can add this little line to suppress message-affected lines:
SET NOCOUNT ON
Then create a cursor for that table and a variable to store the table name
DECLARE @TABLENAME VARCHAR
DECLARE tables_cursor CURSOR FOR SELECT name FROM @tablenames
Then loop through the cursor and run the code for each table name
OPEN Tables_cursor
FETCH NEXT FROM Tables_cursor INTO @Tablename
WHILE @@FETCH_STATUS = 0
BEGIN
YOUR CODE USING THE @Tablename
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor
source to share