Mysql动态sql失误

mysql动态sql出错
CREATE PROCEDURE usp_get_game_by_id(
 v_startRow int, 
 v_rowCount int,
 inout v_gameid int,  
 out v_name varchar(20),
 out v_url varchar(500),
 out v_imagePath varchar(100),
 out v_viewCount int,
 out v_count int,
 out v_categoryId int,
 out v_categoryName varchar(20)
)
begin
 declare v_sql_slc_comment varchar(400); 
 declare v_sql_slc_game_category varchar(400); 
 declare v_sql_slc_count varchar(400);
  set v_sql_slc_comment = ‘select Id,Commenter,Content,CommentTime from t_comment where gameid=? order by CommentTime desc limit ?,?’;  
  set v_sql_slc_game_category = ‘select g.Id,g.Name,g.CategoryId,g.ViewCount,g.Url,g.ImagePath,c.CategoryName CategoryName 
  into ?,?,?,?,?,?,? from t_game g 
  join t_category c on g.CategoryId=c.Id where g.Id = ?’;  
  set v_sql_slc_count = ‘select count(1) into ? from t_comment where gameid=?’;
  set @v_sql_slc_comment = v_sql_slc_comment;  
  set @v_sql_slc_game_category = v_sql_slc_game_category;  
  set @v_sql_slc_count = v_sql_slc_count;
  set @gameid = v_gameid;  
  set @name = v_name;  
  set @categoryId = v_categoryId;  
  set @viewCount = v_viewCount;  
  set @url = v_url;  
  set @imagePath = v_imagePath;  
  set @categoryName = v_categoryName;
  set @startRow = v_startRow-1;  
  set @rowCount = v_rowCount;
  prepare stmt_slc_comment from @v_sql_slc_comment;  
  prepare stmt_slc_game_category from @v_sql_slc_game_category;  
  prepare stmr_slc_count from @v_sql_slc_count;
  EXECUTE stmt_slc_comment using @gameid,@startRow,@rowCount;  
  EXECUTE stmt_slc_game_category using @gameid,@name,@categoryId,@viewCount,@url,@imagePath,@categoryName,@gameid;  
  EXECUTE stmr_slc_count using @viewCount,@gameid;
end;

出错信息：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?,?,?,?,?,?,? from t_game g 
  join t_category c’ at line 2

——解决方案——————–
你的代码执行并无错误！

mysql> delimiter //
mysql> CREATE PROCEDURE usp_get_game_by_id(
-> v_startRow int,
-> v_rowCount int,
-> inout v_gameid int,
-> out v_name varchar(20),
-> out v_url varchar(500),
-> out v_imagePath varchar(100),
-> out v_viewCount int,
-> out v_count int,
-> out v_categoryId int,
-> out v_categoryName varchar(20)
-> )
-> begin
-> declare v_sql_slc_comment varchar(400);
-> declare v_sql_slc_game_category varchar(400);
-> declare v_sql_slc_count varchar(400);
-> set v_sql_slc_comment = ‘select Id,Commenter,Content,CommentTime from t
_comment where gameid=? order by CommentTime desc limit ?,?’;
-> set v_sql_slc_game_category = ‘select g.Id,g.Name,g.CategoryId,g.ViewCo
unt,g.Url,g.ImagePath,c.CategoryName CategoryName
‘> into ?,?,?,?,?,?,? from t_game g
‘> join t_category c on g.CategoryId=c.Id where g.Id = ?’;
-> set v_sql_slc_count = ‘select count(1) into ? from t_comment where game
id=?’;
-> set @v_sql_slc_comment = v_sql_slc_comment;
-> set @v_sql_slc_game_category = v_sql_slc_game_category;
-> set @v_sql_slc_count = v_sql_slc_count;
-> set @gameid = v_gameid;

喜欢 (0)赏

[搞代码]

分享 (0)