求教mysql存储过程有关问题

求教mysql存储过程问题
获取数据表字段列表的存储过程： 
DELIMITER $$ 
DROP PROCEDURE IF EXISTS VAR$$ 

CREATE DEFINER=’root’@’localhost’ PROCEDURE VAR(IN tablename VARCHAR(50)) 
BEGIN 
  SET @sql=CONCAT(‘select column_name from information_schema.columns where  
table_name=’,tablename);  
  PREPARE stmt FROM @sql; 
  EXECUTE stmt; 
  DEALLOCATE PREPARE stmt; 

END$$ 

DELIMITER ; 

调用时CALL VAR(‘fruits’); 
总显示unkonwn column ‘fruits’ in ‘where clause’ 

求解。

——解决方案——————–
DELIMITER $$
DROP PROCEDURE IF EXISTS VAR$$

CREATE DEFINER=’root’@’localhost’ PROCEDURE VAR(IN tablename VARCHAR(50))
BEGIN
SET @SQL=CONCAT(‘select column_name from information_schema.columns where
table_name=/”,tablename,’/”);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;
——解决方案——————–

CREATE DEFINER=’root’@’localhost’ PROCEDURE VAR(IN tablename VARCHAR(50))
BEGIN
SET @sql=CONCAT(‘select column_name from information_schema.columns where 
table_name=’,tablename); 
select @sql
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$

DELIMI

输出你的@sql的内容你就明白了。

喜欢 (0)赏

[搞代码]

分享 (0)