PRB: Process All Results to Find Number of Rows Affected

kbprg The information in this article applies to:

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SYMPTOMS

In a DB-Library (DB-Lib) application that performs SELECT statements that return large numbers of result rows, the following procedure is being used to find out how many rows are returned without processing the entire query.

If a SELECT statement is performed by calling the DBSQLEXEC() and DBRESULTS() functions, and then calling the DBCANCEL() function to cancel the query without processing the rows, a subsequent call to the DBCOUNT() function returns an incorrect number of rows.

CAUSE

This functionality is not supported by the Microsoft SQL Server and DB-Library.

WORKAROUND

In the case above, the number of rows returned by DBCOUNT() is the number of rows that were sent to the workstation before the query was canceled. This also occurs in SAF when the F6 key, which aborts processing of the query, is pressed before the query completes. A subsequent "SELECT @@rowcount" shows the number of rows sent to the workstation before the query was aborted.

To find the number of rows that would be returned without looping through each row in a DB-Lib application, issue the command "SELECT COUNT(*)" with the same WHERE clause as the original SELECT statement. This command is faster than processing each row, because only one row is returned from the SQL Server.