Search code examples
phpsql-serverrows-affected

PHP sql_srv_rows_affected to return multiple outputs

I have created a stored procedure to import a list of records. This stored procedure INSERTs to a temporary table then MERGEs based on if contract numbers match. WHEN NOT MATCHED BY TARGET it INSERTs into the real table and WHEN NOT MATCHED BY SOURCE it DELETEs from the real table.

This works fine, the problem I am working with is in PHP trying to get it to echo the the SQL SERVER messages:

(2384 row(s) affected)

(2 row(s) affected)

The first message of "(2384 row(s) affected)" shows up with sql_srv_rows_affected, but the second one "(2 row(s) affected)" does not. Which the first one is the import into the temporary table and the second result is the amount of rows that were affected by the MERGE statement.

Here is my code to echo out rows affected:

$rows_affected = sqlsrv_rows_affected($Result);
if( $rows_affected === false) {
    die( print_r( sqlsrv_errors(), true));
} 
elseif( $rows_affected == -1) {
    echo "No information available.<br />";
} 
else {
    echo $rows_affected." rows were updated.<br />";
}

Am I able to do a while statement to achieve what I am looking for?

Thank you in advance!

EDIT: Here is the code that worked:

$rows_affected = sqlsrv_rows_affected( $PPListImportResult);
$next_result = sqlsrv_next_result($PPListImportResult);
if( $rows_affected === false) {
    die( print_r( sqlsrv_errors(), true));
} 
elseif( $rows_affected == -1) {
    echo "No information available.<br />";
} 
else {
    echo $rows_affected." rows were updated.<br />";
    echo $next_result." rows were updated.<br />";
}

Which if I had more than 2 results, I could change the $next_result variable into a while statement instead.

Solution

  • For further readers, here is a bit more detailed answer:

    Most RDMSs are able to execute batches (more than one query in one run). From the application's perspective, a stored procedure which contains more than one query is shown as a batch.

    Using SQL Server, all statements (queries) has it's own result in the result set, but most of the functions are fetching the first one by default.

    The application should set the subsequent results active using the library's related function. In the sqlsrv_* function family, this function is the mixed sqlsrv_next_result ( resource $stmt ).

    Makes the next result of the specified statement active. Results include result sets, row counts, and output parameters.

    Returns TRUE if the next result was successfully retrieved, FALSE if an error occurred, and NULL if there are no more results to retrieve.

    Read more about this function in the PHP manual: http://php.net/manual/en/function.sqlsrv-next-result.php

    With an example (without error handling and other fancy stuffs):

    $stmt = sql_server_query($conn, $query, $params);
$affectedRows = array();
do {
  echo 'Rows affected: ' . sqlsrv_rows_affected($stmt)
} while (sqlsrv_next_result($stmt))