Calling Stored Procedure and Display Output in Php MS Sql

Question

i have a problem to display the output of the stored procedure after success run the procedure...i hope everyone can help me....here my stored procedure..

$sql = "EXEC [dbo].[sp_mRetailerRegInfo_Add] '$var1','$var2','$var3','$var4','',''";

This my sample data in my SP

 EXEC sp_mRetailerRegInfo_Add 'asdf','asdf','bacc@abc.com','123', @RetailerId output, @ErrorCode output

and this is my sample code ..i can run the procedure but it's not display the output

<?php

$serverName = "localhost";

$connectionInfo = array( "Database"=>"db", "UID"=>"user", "PWD"=>"pass");
$conn = sqlsrv_connect( $serverName, $connectionInfo);


if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}



//-----------------------------------------------
// Perform operations with connection.
//-----------------------------------------------
$sql = "EXEC [dbo].[sp_mRetailerRegInfo_Add] '$var1','$var2','$var3','$var4','',''";


$result = sqlsrv_query($conn, $sql);
if (!$result) {
    echo 'Your code is fail.';
}
else {
    echo 'Success!';
    echo $result;
}

?>

the output will be like this (ID0001) and but i get the output like this

Connection established.

Success!Resource id #3

Answer 1

With a stored procedure, you would use an OUTPUT parameter that you would assign a value to within the procedure. When the procedure finishes, you would access that parameter and use the contents of it however you need.

You can read through Return Data from a Stored Procedure on the MSDN site to get an idea of how to write a stored procedure.

You will want to take a look at this already answered question that gives some details on how to access the OUTPUT parameter from PHP. You would want the second answer there:

The second param of execute needs to be true, rather than conn. This should work:

$conn = mssql_connect('server', 'user', 'pass');
mssql_select_db('db', $conn);

$procedure = mssql_init('usp_StoredProc', $conn);

$tmpVar1 = 'value';
$tmpVar2 = 'value2';

$outVar1 = '';
$outVar2 = '';

mssql_bind($procedure, "@var1", $tmpVar1, SQLVARCHAR, false, false);
mssql_bind($procedure, "@var2", $tmpVar2, SQLVARCHAR, false, false);

mssql_bind($procedure, "@outVar1", $outVar1, SQLVARCHAR, true);
mssql_bind($procedure, "@outVar2", $outVar2, SQLVARCHAR, true);

mssql_execute($procedure,true);

print($outVar1);
print($outVar2);

If you could provide the definition for your stored procedure, I could elaborate more specifically on how to set up your PHP call.

Answer 2

Well, it's possible. There's a function called mssql_bind for binding the value to a PHP variable

To Call the SP and to store the Output. Try something like this

DECLARE @appout int; -- variable to hold the data returned by SP.

EXEC checkFollowing 10,20, @output = @appout OUTPUT; -- This is how i will call a SP.

@appout - will hold the data returned by the procedure.

So you can do execute your output parameter as below :

$outVar1 = '';

mssql_bind($procedure, "@appout", $outVar1, SQLVARCHAR, true);

mssql_execute($procedure,true);

print($outVar1);