CODEWITHSUNDEEP
mysql
Kudakwashe Moyo
Kudakwashe Moyo

Reputation: 21

Sql variable on select query

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

Upvotes: 0

Views: 58

Answers (3)

Khadim Ali
Khadim Ali

Reputation: 2598

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')

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

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

Upvotes: 0

BlitZ
BlitZ

Reputation: 12168

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`();

Upvotes: 1

Related Questions