不行$query="exec proc_report";这应该是mssql的掉用方法把。 直接call用一下错误 PROCEDURE projectsupport.proc_S_Edit_report can't return a result set in the given context
Only mysqli functions can call sp in mysql. Try mysqli_query().
mysql function can call sp in mysql also! but the result can't return a result set! just can do this(the example just for test) CREATE PROCEDURE `proc_report`( reportId int,out a int) begin select count(id) into a from report where ID = reportId; end; mysql_query('call proc_report($reportid,@a)'); $query = mysql_query('select @a'); $row = mysql_fetch_array($query); print_r($row);
可以使用游标,将结果集的数据用某一特殊字符连接成一个字符串输出,在php里面用explode来操作
CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE c text; DECLARE cur1 CURSOR FOR SELECT data FROM test.t1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO a; set c = c+a; UNTIL done END REPEAT; CLOSE cur1; END
改下。不知道是不是你要的 CREATE PROCEDURE curdemo(out c text) BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE cur1 CURSOR FOR SELECT data FROM test.t1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;OPEN cur1;REPEAT FETCH cur1 INTO a; set c = c+a+'#'; UNTIL done END REPEAT;CLOSE cur1; END
直接call用一下错误
PROCEDURE projectsupport.proc_S_Edit_report can't return a result set in the given context
Try mysqli_query().
but the result can't return a result set!
just can do this(the example just for test)
CREATE PROCEDURE `proc_report`( reportId int,out a int)
begin
select count(id) into a from report where ID = reportId;
end;
mysql_query('call proc_report($reportid,@a)');
$query = mysql_query('select @a');
$row = mysql_fetch_array($query);
print_r($row);
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE c text;
DECLARE cur1 CURSOR FOR SELECT data FROM test.t1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT
FETCH cur1 INTO a;
set c = c+a;
UNTIL done END REPEAT; CLOSE cur1;
END
CREATE PROCEDURE curdemo(out c text)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE cur1 CURSOR FOR SELECT data FROM test.t1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;OPEN cur1;REPEAT
FETCH cur1 INTO a;
set c = c+a+'#';
UNTIL done END REPEAT;CLOSE cur1;
END