CREATE PROCEDURE `procUserlogin`(
out o_userid int,
out o_userstep int,
out o_usernichen varchar(50),
in i_userName varchar(50),
in i_userPasword varchar(50))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
beginset @sqlstr = concat("select @o_userid := user_id from user_list where user_loginname='",i_userName,"'");
prepare sqlstmt from @sqlstr;
execute sqlstmt;
DEALLOCATE PREPARE sqlstmt;
set o_userid = @o_userid;if o_userid = 0 then
set o_userid = -2;
else
set o_userid=0;
set @sqlstr = concat("select @o_userid := user_id from user_list where user_loginname='",i_userName,"' and user_password='",i_userPasword,"'");
prepare sqlstmt from @sqlstr;
execute sqlstmt;
DEALLOCATE PREPARE sqlstmt;
set o_userid = @o_userid;
if @o_userid = 0 then
set o_userid = -1;
else
set @sqlstr = concat("select @o_userstep:=user_state,@o_usernichen:=user_nichenname from user_list where user_id=",o_userid);
prepare sqlstmt from @sqlstr;
execute sqlstmt;
DEALLOCATE PREPARE sqlstmt;
set o_userstep = @o_userstep;
set o_usernichen = @o_usernichen; end if;
end if;我的存储过程在EMS SQL Manage下测试没有问题,在PHP下调用就出现了:
===============================================================================
Commands out of sync; you can't run this command now?
out o_userid int,
out o_userstep int,
out o_usernichen varchar(50),
in i_userName varchar(50),
in i_userPasword varchar(50))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
beginset @sqlstr = concat("select @o_userid := user_id from user_list where user_loginname='",i_userName,"'");
prepare sqlstmt from @sqlstr;
execute sqlstmt;
DEALLOCATE PREPARE sqlstmt;
set o_userid = @o_userid;if o_userid = 0 then
set o_userid = -2;
else
set o_userid=0;
set @sqlstr = concat("select @o_userid := user_id from user_list where user_loginname='",i_userName,"' and user_password='",i_userPasword,"'");
prepare sqlstmt from @sqlstr;
execute sqlstmt;
DEALLOCATE PREPARE sqlstmt;
set o_userid = @o_userid;
if @o_userid = 0 then
set o_userid = -1;
else
set @sqlstr = concat("select @o_userstep:=user_state,@o_usernichen:=user_nichenname from user_list where user_id=",o_userid);
prepare sqlstmt from @sqlstr;
execute sqlstmt;
DEALLOCATE PREPARE sqlstmt;
set o_userstep = @o_userstep;
set o_usernichen = @o_usernichen; end if;
end if;我的存储过程在EMS SQL Manage下测试没有问题,在PHP下调用就出现了:
===============================================================================
Commands out of sync; you can't run this command now?
谢谢指点,发现动态查询有问题,是不是我的写的动态SQL语句的组合有问题?
prepare sqlstmt from @sqlstr;
execute sqlstmt;
DEALLOCATE PREPARE sqlstmt;
set o_userid = @o_userid;
select user_id into @o_userid from user_list where user_loginname=i_userName
谢谢回复user_id into @o_userid 可以传回值?
非常感谢,into @o_userid 可以取得值 @o_userid:=就会出错,请问原因?