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
