在PHP上的EXEC之后，过程返回代码不起作用

I have written a procedure that returns a code after going through its code. I also have a PHP function using the sqlsrv library to fetch data. I am able to get the correct returncode from SQL Server Management Studio, However, in the php code, the returncode is not updated with the actual return value.

$returncode = 10000;
$procedure = "{call AccountBulkChargeTest( ?, ?, ?, ? )}";
$params = array( 
                         array($username, SQLSRV_PARAM_IN),
                         array($nonumber, SQLSRV_PARAM_IN),
                         array($messages, SQLSRV_PARAM_IN),
                         array($returncode, SQLSRV_PARAM_OUT)
            );
$stmt = sqlsrv_query( $this->db, $procedure, $params );
var_dump($returncode);

This returns the following (no change, supposed to be 1000:

int(10000)

However, running the same in SQL Server Management Studio with: USE [Database] GO

DECLARE @return_value int,
    @ReturnCode int

EXEC @return_value = [dbo].[AccountBulkChargeTest]
    @Username = N'hello',
    @Nonumber = 1,
    @Messages = N'hello',
    @ReturnCode = @ReturnCode OUTPUT

SELECT @ReturnCode as N'@ReturnCode'

SELECT 'Return Value' = @return_value

GO

Returns @ReturnCode as 1000 and Return Value as 0;

My procedure is as below:

ALTER procedure [dbo].[AccountBulkChargeTest]
(
@Username   varchar(32),
@Nonumber   int,
@Messages   varchar(640),
@ReturnCode int output
)
AS
BEGIN

DECLARE @Value int = 20;

IF(@Value < 25)
    BEGIN
        SET @ReturnCode = 1000;
        UPDATE LOGIN SET BALANCE = BALANCE - 0;
        EXEC AccountDoTransaction 0, 0, 0, 0;
        RETURN;
    END
ELSE IF(@Value < 40)
    BEGIN
        SET @ReturnCode = 2000;
    END
ELSE
    BEGIN
        SET @ReturnCode = 3000;
    END
END
RETURN;

I should not that removing the following lines seems to make it work but I need to be able to call to that procedure within the one posted above:

UPDATE LOGIN SET BALANCE = BALANCE - 0;
EXEC AccountDoTransaction 0, 0, 0, 0;

Your stored procedure needs to use the RETURN keyword in order to return a value to @return_value.

As per the MSDN documentation you will need to use the RETURN @ReturnCode style.

A SQL Server stored procedure can return information in a result set, output parameters or an integer return code. The integer return code usually contains the number of records affected by the last query. But you can return a custom value with the return statement.

Your stored procedure has an output parameter that is called @ReturnCode. But the name of a variable does not change its nature. It's still an output parameter.

You could try to retrieve the real return code with a PHP-semi-SQL statement like:

{call ? = YourProc()}

For example:

$retval = -1;
$par1 = 'will the real return code please stand up';
$procedure = "{call ? = YourProc(?)}";
$params = array( 
    array($retval, SQLSRV_PARAM_INOUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT),
    array($par1, SQLSRV_PARAM_IN)
);
$stmt = sqlsrv_query( $this->db, $procedure, $params );
var_dump($retval);

But unless your procedure contains statements like return 42, the return value is unlikely to contain anything useful.