php - sqlsrv_has_rows not working with Stored Procedures. Is there a way round this?
Get the solution ↓↓↓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 functionsqlsrv_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&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
Solution:
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 ...;
, notexec.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&2=<?php echo($ROW_Contact01['ContactResolveId']);?>">Details</a></td>
</tr>
<?php
}
}
?>
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: illuminate\http\exceptions\posttoolargeexception
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.