hey lets say we have 3 tables tbl1,tbl2,tbl3 and using the below query you can view the data
select * from tbl1
select * from tbl2
select * from tbl3
my question is that can you eliminate repetition by using variables? like for example
DECLARE @x as varchar(60)
set @x = tbl1
set @x = tbl2
set @x = tbl3
select * from @x
Go
I hate retyping the exact query does anyone know a way to make the query work I think it would save me loads of time. thanks
Why not create a stored procedure for that?
Example:
DELIMITER $$
DROP PROCEDURE IF EXISTS `selectAllTables`$$
CREATE PROCEDURE `selectAllTables`()
BEGIN
SELECT * from tbl1;
SELECT * from tbl2;
SELECT * from tbl3;
END$$
DELIMITER ;
Usage:
CALL `selectAllTables`();
If you meant Dynamic SQL (SQL Server) type of support in MySQL, you may achieve this using prepare statement in stored procedure
First create a stored procedure with a parameter to take table name in string:
DELIMITER ///
CREATE PROCEDURE SelectAllRecords(__TABLE__NAME varchar(255))
BEGIN
SET @sql = CONCAT('SELECT * FROM ', __TABLE__NAME);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END///
DELIMITER ;
Next, you may CALL the stored procedure sending in the table name:
call SelectAllRecords('table1')
call SelectAllRecords('table2')
call SelectAllRecords('table3')
If i got your Question right then the below Query will work for you.
DECLARE @x as table
set @x = select * from tbl3
select * from @x
Go
