nikomaster nikomaster - 7 months ago 19
SQL Question

sql run querys since procedures

I tried do query since one procedure in mysql

I followed the following:

DELIMITER #

CREATE PROCEDURE returndata(IN xtable CHAR(255), IN xcolumn CHAR(255))
BEGIN
IF (xcolumn = 'XALL') THEN
SELECT * FROM xtable;
ELSE
SELECT xcolumn FROM xtable;
END IF;
END;
#

DELIMITER ;


but gives error. any help is acceptable, or I might say if this is possible?

EDIT error to call the procedure:

MariaDB [pruebab]> CALL returndata('test', 'id');
ERROR 1146 (42S02): Table 'pruebab.xtable' doesn't exist

Answer

Variables are only evaluated in expressions in queries, not where column or table names are required. You need to use a prepared query.

CREATE PROCEDURE returndata(IN xtable CHAR(255), IN xcolumn CHAR(255))
    BEGIN
        IF (xcolumn = 'XALL') THEN
            SET @SQL = CONCAT('SELECT * FROM ', xtable);
        ELSE
            SET @SQL = CONCAT('SELECT ', xcolumn, ' FROM ', xtable);
        END IF;
        PREPARE stmt FROM @SQL;
        EXECUTE stmt;
    END;
Comments