Microsoft Drivers for PHP for SQL Server version 2.0 and 3.0
sqlsrv_num_rows
Reports the number of rows in a result set.
Syntax
sqlsrv_num_rows( resource $stmt )
Parameters
$stmt: The result set for which to count the rows.
Return Value
false if there was an error calculating the number of rows. Otherwise, returns the number of rows in the result set.
Remarks
sqlsrv_num_rows requires a client-side, static, or keyset cursor, and will return false if you use a forward cursor or a dynamic cursor. (A forward cursor is the default.) For more information about cursors, see sqlsrv_query and Cursor Types (SQLSRV Driver).
Example
<?php
$server = "server_name";
$conn = sqlsrv_connect( $server, array( 'Database' => 'Northwind' ) );
$stmt = sqlsrv_query( $conn, "select * from orders where CustomerID = 'VINET'" , array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$row_count = sqlsrv_num_rows( $stmt );
if ($row_count === false)
echo "\nerror\n";
else if ($row_count >=0)
echo "\n$row_count\n";
?>
The following sample shows that when there is more than one result set (a batch query), the number of rows is only available when you use a client-side cursor.
<?php
$serverName = "(local)";
$connectionInfo = array("Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$tsql = "select * from HumanResources.Department";
// Client-side cursor and batch statements
$tsql = "select top 2 * from HumanResources.Employee;Select top 3 * from HumanResources.EmployeeAddress";
// works
$stmt = sqlsrv_query($conn, $tsql, array(), array("Scrollable"=>"buffered"));
// fails
// $stmt = sqlsrv_query($conn, $tsql);
// $stmt = sqlsrv_query($conn, $tsql, array(), array("Scrollable"=>"forward"));
// $stmt = sqlsrv_query($conn, $tsql, array(), array("Scrollable"=>"static"));
// $stmt = sqlsrv_query($conn, $tsql, array(), array("Scrollable"=>"keyset"));
// $stmt = sqlsrv_query($conn, $tsql, array(), array("Scrollable"=>"dynamic"));
$row_count = sqlsrv_num_rows( $stmt );
echo "\nRow count for first result set = $row_count\n";
sqlsrv_next_result($stmt);
$row_count = sqlsrv_num_rows( $stmt );
echo "\nRow count for second result set = $row_count\n";
?>
See Also
Other Resources
Community Content
Immanuel Dogman
sqlsrv_num_rows() with stored procedures
[NOTE: also posted in SQL Server 2005]
Is there a way to use sqlsrv_num_rows() with stored procedures?
When a cursor (ie SQLSRV_CURSOR_KEYSET or STATIC) is set in the db connection options, as is required, the following notice is returned without a num_row result:
Obviously, I'm great at neither PHP or T_SQL so really need help with this. So far I've come across three options which I wasn't too keen on trying either because they aren't quite effiecient or I lack the know-how / control of the SQL database:
Is there a way to use sqlsrv_num_rows() with stored procedures?
When a cursor (ie SQLSRV_CURSOR_KEYSET or STATIC) is set in the db connection options, as is required, the following notice is returned without a num_row result:
Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 16954 [code] => 16954 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. ) )
Obviously, I'm great at neither PHP or T_SQL so really need help with this. So far I've come across three options which I wasn't too keen on trying either because they aren't quite effiecient or I lack the know-how / control of the SQL database:
- using a "count()" function
- passing a parameter from containing row count through the SP;
- creating a VIEW for the results of the SP, then applying sqlsrv_num_rows() on this VIEW