SQL Server not returning a value from stored procedure

Question

I try to used PHP to Connect to SQL Server to call a procedure in it. But there no result return back. Please give me some advise.

Example Stored Procedure:

ALTER  PROCEDURE [MemberJoin]
    @username varchar(25),
    @password varchar(50),
AS
If (EXISTS(SELECT username FROM tbl_user WHERE username = @username ))
    Begin
        RETURN -2
    End
BEGIN TRANSACTION
BEGIN
   INSERT INTO tbl_user (username , password ) VALUES (@username, @password)
    If (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    Begin
        ROLLBACK TRANSACTION
        RETURN -3
    End
COMMIT TRANSACTION
END

RETURN 1

PHP Code

$return = null;

$server = "LOCALHOST\\SQLEXPRESS";
$connectionInfo = array( "Database"=>"SHOP", "UID"=>"sa", "PWD"=>"");
$Conn = sqlsrv_connect( $server, $connectionInfo);

$sql = "{call ? = MemberJoin(@username=?,@password=?)}";

$param = [
    [&$return, SQLSRV_PARAM_OUT],
    ['testingsr', SQLSRV_PARAM_IN],
    ['12345667', SQLSRV_PARAM_IN],
];

$stmt = sqlsrv_query ( $Conn , $sql, $param );

echo($return);

Result null with no error. I expect that return values to be 1,-2 or -3

Thank you in advance.

Have you tried [&$return, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT]? Ref: sqlsrv_prepare — AlwaysLearning
– AlwaysLearning, Commented Aug 22, 2020 at 6:26

vvvv4d · Accepted Answer · 2020-08-22 06:19:12Z

1

You need to declare a variable to capture the return value and then check the value of the variable. Below is an example.

DECLARE @return_status int;  
EXEC @return_status = dbo.sproc_checkstate;  
SELECT 'Return Status' = @return_status;

For what you are doing it may be easier to return a column. For example you can change RETURN -2 to SELECT -2 as return_status and return the value as a return_status column in a resultset.

answered Aug 22, 2020 at 6:19

vvvv4d

4,1351 gold badge18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wanatat Chanjaem Over a year ago

Thank you so much, I'd like to change the procedure code too. But I can't change the SQL code. :)

Collectives™ on Stack Overflow

SQL Server not returning a value from stored procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related