sqlsrv_has_rows not working with Stored Procedures. Is there a way round this?

I am converting a few legacy HTML forms from ASP to PHP. The data is retrieved from Stored Procedures on SQL Server 2017.

The reason we use SP’s is that some of the queries require table variables and use of ‘with CTE’ to produce the end results.

The issue I have is that I need to check if rows are returned and if not display a suitable HTML message. The function sqlsrv_has_rows() works just fine for standard SELECT statements but not for SP’s. I am led to believe that this is an issue with the SQL Native Driver and not PHP.

Here is an example of what is NOT working.

<?php
    $SQLStmt = "exec.dbo.usp_QueryContacts NULL,".$parm1.",NULL;";
    $RS_Contact01 = sqlsrv_query($conn01, $SQLStmt); 
    if (sqlsrv_has_rows($RS_Contact01) === false) {
?>
                    <p>There are currently no contacts on record for this Site.</p> 
<?php
    }
    else {
?>
<?php
    while ($ROW_Contact01 = sqlsrv_fetch_array($RS_Contact01,SQLSRV_FETCH_ASSOC)) {
?>
                    <tr class="tablebody">
                    <td><?php echo($name);?></td>
                    <td><?php echo($ROW_Contact01['Email']);?></td>
                    <td><?php echo($ROW_Contact01['Phone']);?></td>
                    <td><?php echo($ROW_Contact01['Mobile']);?></td>
                    <td><a class="linkbutton shuttlegray shuttlegrayhover" href="contact-det.php?1=1&amp;2=<?php echo($ROW_Contact01['ContactResolveId']);?>">Details</a></td>
                    </tr>
<?php
        }
    }
?>

This throws up a:

‘sqlsrv_num_rows() expects parameter 1 to be resource, bool given’

warning.

I can find all sorts of threads mentioning turning on ‘SET NOCOUNT ON;` etc but I am struggling to find an actual workaround/solution that allows me to detect when no rows are returned and act accordingly.

Any practical help for a novice PHP coder would be be greatly appreciated (including best practice).

Answer

The reason for the “sqlsrv_num_rows() expects parameter 1 to be resource, bool given” error is that the statement is not executed correctly. It’s probably a typing error (exec dbo.usp_QueryContacts ...;, not exec.dbo.usp_QueryContacts ...;), but you need to consider the following:

Always use parameters in your statements to prevent possible SQL-injection issues. As is mentioned in the documentation, the … sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply … and the … sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.
Always check the result from the sqlsrv_query() call.

The example below (based on your code) is a possible solution to your problem:

<?php
    $SQLStmt = "exec dbo.usp_QueryContacts NULL, ?, NULL;";
    $SQLPrms = array($parm1);
    $RS_Contact01 = sqlsrv_query($conn01, $SQLStmt, $SQLPrms);
    if ($RS_Contact01 === false) {
        echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
        exit;
    }

    if (sqlsrv_has_rows($RS_Contact01) === false) {
?>
    <p>There are currently no contacts on record for this Site.</p> 
<?php
    } else {
    while ($ROW_Contact01 = sqlsrv_fetch_array($RS_Contact01, SQLSRV_FETCH_ASSOC)) {
?>
    <tr class="tablebody">
    <td><?php echo($name);?></td>
    <td><?php echo($ROW_Contact01['Email']);?></td>
    <td><?php echo($ROW_Contact01['Phone']);?></td>
    <td><?php echo($ROW_Contact01['Mobile']);?></td>
    <td><a class="linkbutton shuttlegray shuttlegrayhover" href="contact-det.php?1=1&amp;2=<?php echo($ROW_Contact01['ContactResolveId']);?>">Details</a></td>
    </tr>
<?php
        }
    }
?>

Advertisement

Answer