CODEWITHSUNDEEP
phpsql-serversqlsrv
Wanatat Chanjaem
Wanatat Chanjaem

Reputation: 13

SQL Server not returning a value from stored procedure

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.

Upvotes: 0

Views: 586

Answers (1)

vvvv4d
vvvv4d

Reputation: 4095

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.

Upvotes: 1

Related Questions