sql run querys since procedures

Question

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

Can you add the error message your are getting? – Hyder B. Apr 22 '16 at 23:05 — Hyder B., Apr 22 '16 at 23:05

score 1 · Answer 1 · edited May 23 '17 at 10:30

1

You cannot pass a table name as a parameter like that. You need to concatenate the variables into an SQL string to use them. See this answer.

Mysql stored procedure don't take table name as parameter

edited May 23 '17 at 10:30

Community

1
1

answered Apr 22 '16 at 23:24

Harry

11,298
1
29
43

ok, I look like a fool, thanks for your answer. – MindLerp Apr 22 '16 at 23:28
No you don't. We're all learning. – Harry Apr 22 '16 at 23:28

score 0 · Accepted Answer · answered Apr 22 '16 at 23:28

0

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;

answered Apr 22 '16 at 23:28

Barmar

741,623
53
500
612

I really worked, i did not know about the sentence: PREPARE stmt FROM @SQL; EXECUTE stmt; - i will investigate more about it, thx. – MindLerp Apr 22 '16 at 23:36

sql run querys since procedures

2 Answers2