doupao1978
2014-08-20 19:12
浏览 46
已采纳

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条回答 默认 最新

  • drxnfdx798517235 2014-08-20 19:38
    最佳回答

    New:

    I missed that you are using a single parameter as both input and output. Please try the following.

    array($outSeq, SQLSRV_PARAM_INOUT)
    

    Then using

    sqlsrv_next_result($stmt);
    echo $outSeq;
    

    Reference: http://technet.microsoft.com/en-us/library/cc644932(v=sql.105).aspx

    Old:

    You must set up $outSeq with the appropriate data type. Try initialize the value to $outSeq = 0.00, since your output type is MONEY.

    Please reference the following article:

    http://technet.microsoft.com/en-us/library/cc626303(v=sql.105).aspx

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题