Call SQL Server stored procedure with output parameter from PHP

Question

I am trying to execute a sql server stored procedure from php. Stored procedure returns a output value. I want that value in php to display in webpage.

Everything works fine except the output value returns 0 in php.. but the row is getting inserted perfectly. the output value shows as 0. annoyed where I have made the mistake. Below is my php code

require('db.php');   

    $billno = "REF0001";  
    $retsno = 0.0;  

    $sp_name = "{call testinsert( ?, ? )}";

    $params = array(   
                     array($billno, SQLSRV_PARAM_IN),  
                     array($retsno, SQLSRV_PARAM_OUT)  
                   );  

    /* Execute the query. */  
    $stmt3 = sqlsrv_query( $conn, $sp_name, $params);  
    if( $stmt3 === false )  
    {  
         echo "Error in executing statement 3.\n";  
         die( print_r( sqlsrv_errors(), true));  
    }  


    echo "Inserted Retuern Sno for ".$billno." is ". $retsno. ".";

and my stored procedure is

create procedure testinsert(@bill_no VARCHAR(20),@RetSno INT OUTPUT)
as
begin
    insert into testable values(@bill_no,GETDATE())
    set @RetSno = @@IDENTITY
    return
end

Those variables aren't being modified so when you echo them you get their initial values (REF0001, 0,0). you need to assign the updated values to those variables. Or am I missing something? — Ofir Baruch, Jan 31 '17 at 08:50
PLease try if you get the correct response when you replace the `@@IDENTITY` with a normal number. — Andy, Jan 31 '17 at 08:53
Andy I tried that too. i simply returned a vague value from stored procedure. still it returns 0. — Srinivasan, Jan 31 '17 at 08:56
@OfirBaruch is right, `sqlsrv_query` just uses your `$params`-Array but doesn't overwrite anything in it — Andy, Jan 31 '17 at 08:58
May you try this: http://stackoverflow.com/a/8355639/4190467 with the MS PHP-Driver — Andy, Jan 31 '17 at 08:59
I might be stupid to suggest this, but would `$params = [ [ $billno, SQLSRV_PARAM_IN] , [ &$retsno, SQLSRV_PARAM_OUT ] ];` work? — apokryfos, Jan 31 '17 at 09:02
Try to modify your stored procedure and use "sqlsrv_fetch_array($stmt3)" — Ofir Baruch, Jan 31 '17 at 09:03
The solution of @OfirBaruch should work. You can use after the fetch-Statement `var_dump($stmt3);` to see what you got as the response — Andy, Jan 31 '17 at 09:11

score 0 · Answer 1 · answered Jan 31 '17 at 09:14

0

Maybe you missed scrollable. https://msdn.microsoft.com/en-us/library/hh487160(v=sql.105).aspx

$stmt3 = sqlsrv_query( $conn, $sp_name, $params, array( "Scrollable" => 'static'));

answered Jan 31 '17 at 09:14

Manikandan

502
1
7
17

score 0 · Answer 2 · answered Jan 31 '17 at 09:25

0

Before you use your echo, add the following line to read the next line:

sqlsrv_fetch($stmt3);

Then you can select the first field of the response, which should be your parameter, with sqlsrv_get_field():

$id = sqlsrv_get_field(0);

answered Jan 31 '17 at 09:25

Andy

393
3
16

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

I had the same case, and solved it using:

sqlsrv_next_result($stmt);

at the end of the instruction. Here I put part of my code in case it helps you.



    $totalPaginas = 0;
    $result = array();
    $query = "EXEC DBO.SPS_EJECUTAR_CONSULTA @ReporteID = ?, @FilasPorPagina = ?, @NroPagina = ?, @TotalPaginas = ?";
    $params = array(
        array(&$reporteid, SQLSRV_PARAM_IN),
        array(&$filasPorPagina, SQLSRV_PARAM_IN),
        array(&$nroPagina, SQLSRV_PARAM_IN),
        array(&$totalPaginas, SQLSRV_PARAM_OUT)
    );
    $stmt = sqlsrv_prepare($this->db, $query, $params);
    if( !$stmt ) {
        // show errors
    }
    $result = sqlsrv_execute($stmt);
    if( !$result ) {
        // show errors
    }
    $data = array();
    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
        $data[] = $row;
    }
    sqlsrv_next_result($stmt);
    var_dum($data);
    echo('Total Paginas: '.$totalPaginas);

Officially tells you how to do it here: https://learn.microsoft.com/en-us/sql/connect/php/how-to-retrieve-input-and-output-parameters-using-the-sqlsrv-driver

Call SQL Server stored procedure with output parameter from PHP

3 Answers3