Retrieving output parameter from SQL server stored procedure

[ivanvishnu]

 How to retrieve a table output parameter from sql server stored procedure to php variable? 

 

I've the following code that returns a table from the query. The table needs to be retrieved in PHP and formatted before being printed onto a file. 

 

## Stored Procedure: 

 

 @query1 has a table from select statement that is pivoted. 

 

       EXEC SP_EXECUTESQL @query= @query1, @params= N'@queryO NVARCHAR(MAX) OUTPUT', @queryO= @queryO OUTPUT; 

 

       declare @getProducts NVARCHAR(MAX) 

 

       select @getProducts= @queryO; 

 

## The PHP code follows: 

   

    $stmt2 = sqlsrv_query($conn, $tsql_createSP);

 

    if( $stmt2 === false )

    {

    echo "Error in executing statement 2.\n";

    die( print_r( sqlsrv_errors(), true));

    }

 

   $getProducts="ABCD";

 

  $params = array(

  array($chrom_set,SQLSRV_PARAM_IN),

  array($getProducts,SQLSRV_PARAM_OUT)

  );

 

  $tsql_callSP = "{call spGetPivot( ?,? OUTPUT)}";

 

  $stmt3 = sqlsrv_query($conn,$tsql_callSP,$params);

 

 

  if ( $stmt3 === false) { echo "Error in executing statement 3.\n";

        die( FormatErrors( sqlsrv_errors() ) );}

 

  $productCount = 0;     

 

  function cleanData($str)

  {

    $str = preg_replace("/\t/", "\\t", $str);

    $str = preg_replace("/\r?\n/", "\\n", $str);

    $str=str_replace("\\n","",$str);

    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';

  }

 

 // file name for download

  

  $filename = "NAM_data_" . date('YmdHis') ."_pg".".txt";

 

  header("Content-Disposition: attachment; filename=\"$filename\"");

  header("Content-Type: text/plain");

 

  $flag = false;

 

  while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC)){

    

  if(!$flag){

      // display field/column names as first row

 

      array_walk($row, 'cleanData');

      echo implode("\t", array_keys($row)) . "\r\n";

      $flag = true;

    }

    array_walk($row, 'cleanData');

    echo implode("\t", array_values($row)). "\r\n";

  } 

  

   sqlsrv_free_stmt( $stmt1);

   sqlsrv_free_stmt( $stmt2);

   sqlsrv_free_stmt( $stmt3);

   sqlsrv_close( $conn);

 

I'm not able to retrieve the output with this code. The query works well in this format in SSMS.