PHP sqlsrv_connect - Example with INPUT, OUTPUT, return value and select combined-CodePudding

Good morning,

I created the following stored procedure on mssql server.

The idea is to have a complete example dealing with

Input parameters ( here an int and php string)
Output parameters (here @RET as number)
Select statement ( here a set of two rows )
Return value (3141592)

So I created the following proc :

ALTER PROCEDURE [dbo].[PROC_TEST]
   -- Add the parameters for the stored procedure here
   @INT int = 3,
   @STR nvarchar(10) = 'foo',
   @RET int OUTPUT   
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      set @RET = 3141592

      -- Insert statements for procedure here
      select getdate() as xdate, @INT as xint, @STR as xstr
      union
      select getdate() as xdate, 3141592 as xint, 'baz' as xstr

      return 3141592
 END

I am lost with the php code to get these datas. I am able to get the selected rows based on my input values, but the output field (@RET) is never retrieved as the return value. My best working code is the one:

$a=55;
$b='foo';
$r=0;
$ret=0;
$procedure_params = array(                                                                                
  array(&$r,  SQLSRV_PARAM_OUT),
  array(&$a,  SQLSRV_PARAM_IN ),
  array(&$b,  SQLSRV_PARAM_IN ),
  array(&$ret,SQLSRV_PARAM_OUT)                                                                                                        
);
                                                            
                                                          
$sql = " EXEC ?=PROC_TEST @INT = ?, @STR = ? , @RET=?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);
if (!sqlsrv_execute($stmt)) {
       //<this is not executed>
}
while($row = sqlsrv_fetch_array($stmt)){
        print_r($row);   //  ok it print the two rows returned                                                             
}
print "\nRETURN VALUES ARE : $r $ret \n"; 
                                                            
sqlsrv_close( $conn );

-> No problem with the select. I retrieve the two rows. Nevertheless, the $r and $ret are definitively stuck at 0

Can someone help me on this topic ?

CodePudding user response：

As is explained in the documentation, ... when retrieving an output or input/output parameter, all results returned by the stored procedure must be consumed before the returned parameter value is accessible. In this situation you need to use sqlsrv_next_result() and an appropriate loop.

<?
...

// Statement
$a   = 55;
$b   = 'foo';
$r   = 0;
$ret = 0;
$procedure_params = array(                                                                                
   array(&$r,   SQLSRV_PARAM_OUT),
   array($a,    SQLSRV_PARAM_IN),
   array($b,    SQLSRV_PARAM_IN),
   array(&$ret, SQLSRV_PARAM_OUT)                                                                                                        
);
                                                          
$sql = "EXEC ? = PROC_TEST @INT = ?, @STR = ? , @RET = ?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);
if (!sqlsrv_execute($stmt)) {
       //<this is not executed>
}
do {
    while ($row = sqlsrv_fetch_array($stmt)) {
        print_r($row);
    }
} while (sqlsrv_next_result($stmt)); 
    
print "\nRETURN VALUES ARE : $r $ret \n"; 
                                                            
// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

...
?>