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?
解决方案 »
- 只读事务在默认REPEATABLE-READ隔离级别下有什么意义?
- sql语句引起的效率问题
- 请教这句oracle如何修改成mysql的
- 有关MYSQL数据库的集群问题
- Mysql 分区能否使用alter
- mysql5.0数据实时更新问题
- select中查询的变量的是个数值型变量怎么办?
- 有谁能告诉我这样select给一个变量赋值有什么问题?在Sql server 中,应该是正确的阿
- @@identity和LAST_INSERT_ID()有什么区别么
- linux下wordpress站点mysql_connect() no such file...
- 部分字段的索引中,Sub_part的数字是byte还是字数?
- Linux 下MY-SQL 支持查询文本吗
谢谢指点,发现动态查询有问题,是不是我的写的动态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:=就会出错,请问原因?