doupao1978
2014-08-20 19:12 阅读 34
已采纳

PHP从MSSQL存储过程获取返回值

I can't get the OUTPUT parameter from my SQL Server (MSSQL 2012) SP to return to PHP. My Stored procedure is:

CREATE PROCEDURE spGetNextSeqID @ID AS INT OUTPUT
AS
BEGIN
    BEGIN TRANSACTION
    SELECT @ID = SEQUENCE_NO + 1 FROM tblCSRSequence WITH (TABLOCKX)
    UPDATE tblCSRSequence SET SEQUENCE_NO=@ID
    COMMIT TRANSACTION
END

And my PHP code is:-

<?php
include "DBConnect.php";
$conn = sqlsrv_connect( $serverName, $connection);
if( !$conn )
{
     echo "Connection could not be established to ".$serverName;
     die( print_r( sqlsrv_errors(), true));
}
$sql="{call dbo.spGetNextSeqID( ? )}";
$outSeq=0;
$params = array
    (
        array($outSeq, SQLSRV_PARAM_OUT)
    );

$stmt = sqlsrv_query( $conn, $sql, $params );

if( $stmt == false)
    die( print_r( sqlsrv_errors(), true) );

sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn );
echo $outseq;
?>

I know the SP is getting called and working - I checked it with a trace and can see that it's generating the following:-

declare @p1 varchar(max)
set @p1='154'
exec dbo.spGetNextSeqID @p1 output
select @p1

Each time I refresh my browser page it calls the SP and increments the counter by 1 but never returns the value to the calling PHP function. I've been fiddling with this for about 2 days now - I've scoured the similar posts but none of the suggested fixes (like SET NOCOUNT ON etc) work.

Anyone got any ideas?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

相关推荐