如何在mysql的存储过程中传递表名作为参数?

hc8w905p  于 2023-01-12  发布在  Mysql
关注(0)|答案(1)|浏览(281)

我正尝试在存储过程中实现下面的查询。

SELECT d.empno, d.fecthIdId, c.empno
FROM MyDatabase.myTable  a
JOIN MyDatabase.myTable b ON a.id = b.xid 
JOIN MyDatabase.Table2 c ON b.Id = cId 
JOIN MyDatabase.Table3 d ON a.bid = d.cid AND d.empId = _empId

存储过程:

DELIMITER $$
CREATE  PROCEDURE `TestSP`(_empId INT,   _myTable VARCHAR(50))
BEGIN

SET @mysql.SQL
= CONCAT('SELECT d.empno, d.fecthIdId, c.empno
FROM MyDatabase.',_myTable,'  a
JOIN MyDatabase.',_myTable,'  b ON a.id = b.xid 
JOIN MyDatabase.Table2 c ON b.Id = cId 
JOIN MyDatabase.Table3 d ON a.bid = d.cid AND d.empId = _empId');

PREPARE dynamicQuery FROM @mysql.SQL;
EXECUTE dynamicQuery;
DEALLOCATE PREPARE dynamicQuery;

END$$
DELIMITER ;

程序调用:

call TestSP(10, 'myTable');

错误:

Error Code: 1113. A table must have at least 1 column

我不知道密码有什么问题。谢谢你的帮助。

mrzz3bfm

mrzz3bfm1#

你会调试你的代码。

CREATE  PROCEDURE `TestSP`(_empId INT,   _myTable VARCHAR(50))
BEGIN

SET @mysql.SQL
= CONCAT('SELECT d.empno, d.fecthIdId, c.empno
FROM MyDatabase.',_myTable,'  a
JOIN MyDatabase.',_myTable,'  b ON a.id = b.xid 
JOIN MyDatabase.Table2 c ON b.Id = cId 
JOIN MyDatabase.Table3 d ON a.bid = d.cid AND d.empId = _empId');
/*
PREPARE dynamicQuery FROM @mysql.SQL;
EXECUTE dynamicQuery;
DEALLOCATE PREPARE dynamicQuery;
*/
SELECT @mysql.SQL `built SQL code`;
END
call TestSP(10, 'myTable');

| 构建的SQL代码|
| - ------|
| 选择d.雇员编号、d.感染标识Id、c.雇员编号 来自我的数据库. myTable a在www.example.com上加入我的数据库.我的表b = b. xida.id在www.example.com上连接我的数据库.表3 d = d. cid和d.雇员Id =_雇员IdJOIN MyDatabase.Table2 c ON b.Id = cId JOIN MyDatabase.Table3 d ON a.bid = d.cid AND d.empId = _empId |
fiddle
现在将此代码复制粘贴到CLI中并尝试执行它。

相关问题