CODEWITHSUNDEEP
phpsql-serverstored-proceduressqlsrv
nashleigh
nashleigh

Reputation: 13

PHP Get return value from MSSQL Stored Procedure

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?

Upvotes: 0

Views: 7593

Answers (1)

Ahz
Ahz

Reputation: 361

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

Upvotes: 1

Related Questions