问题描述

求大神 帮我看下这个存储过程哪里错了

DROP PROCEDURE IF EXISTS Query_Role_PhoneRecord;
DELIMITER ;;
CREATE DEFINER=root@% PROCEDURE Query_Role_PhoneRecord(

in userId binary(16)in pagNo intin pagSize int

)
BEGIN
declare bSize int;

set bSize=(pagNo-1)*pagSize;
if(bSize<0) then
set bSize =0; end if;
SELECT
view_role_phonerecord.ID
view_role_phonerecord.LimId
view_role_phonerecord.Recordor
view_role_phonerecord.RecordTime
view_role_phonerecord.BDID
view_role_phonerecord.Caller
view_role_phonerecord.PhonNo
view_role_phonerecord.CallType
view_role_phonerecord.Record
view_role_phonerecord.IsRecordorActive
FROM cemscenter.view_role_phonerecord
where view_role_phonerecord.UserId=userId
limit bSizepagSize;

END
;;
DELIMITER ;

[Err] 1064 - 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 'bSizepagSize;

END' at line 26

解决方案

limit需要连接或者预编译
如

 PREPARE stmt1 FROM 'select * from users LIMIT ??'; SET @a = ino; SET @b = pagecount EXECUTE stmt1 USING @a @b;deallocate prepare stmt1; or set dd=conact('select * from users LIMIT'inopagecount) PREPARE stmt1 FROM dd EXECUTE stmt1deallocate prepare stmt1;

解决方案二：
if(bSize<0) then
set bSize =0; end if;

改为

if(bSize<0) then
set bSize =0;
end if;