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
– Ofir Baruch, Commented Jan 31, 2017 at 8:50
PLease try if you get the correct response when you replace the @@IDENTITY with a normal number. — Andy
– Andy, Commented Jan 31, 2017 at 8:53
Andy I tried that too. i simply returned a vague value from stored procedure. still it returns 0. — Srinivasan
– Srinivasan, Commented Jan 31, 2017 at 8:56
@OfirBaruch is right, sqlsrv_query just uses your $params-Array but doesn't overwrite anything in it — Andy
– Andy, Commented Jan 31, 2017 at 8:58
Try to modify your stored procedure and use "sqlsrv_fetch_array($stmt3)" — Ofir Baruch
– Ofir Baruch, Commented Jan 31, 2017 at 9:03

Manikandan · Accepted Answer · 2017-01-31 09:14:52Z

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, 2017 at 9:14

Manikandan

5121 gold badge7 silver badges18 bronze badges

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

Comments

Andy · Accepted Answer · 2017-01-31 09:25:44Z

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, 2017 at 9:25

Andy

4032 silver badges17 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

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

Collectives™ on Stack Overflow

Call SQL Server stored procedure with output parameter from PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related