ODBC, PHP and SQL Variables

Question

I have a fairly large, complex chunk of SQL that creates a report for a particular page. This SQL has several variable declarations at the top, which I suspect is giving me problems.

When I get the ODBC result, I have to use odbc_next_result() to get past the empty result sets that seem to be returned with the variables. That seems to be no problem. When I finally get to the "real" result, odbc_num_rows() tells me it has over 12 thousand rows, when in actuality it has 6.

Here is an example of what I'm doing, to give you an idea, without going into details on the class definitions:

$report = $db->execute(Reports::create_sql('sales-report-full'));
while(odbc_num_rows($report) <= 1) {
    odbc_next_result($report);
}
echo odbc_num_rows($report);

The SQL looks something like this:

DECLARE @actualYear int = YEAR(GETDATE());
DECLARE @curYear int = YEAR(GETDATE());
IF MONTH(GETDATE()) = 1
    SELECT @curYear = @curYear - 1;
DECLARE @lastYear int = @curYear-1;
DECLARE @actualLastYear int = @actualYear-1;
DECLARE @tomorrow datetime = DATEADD(dd, 1, GETDATE());
SELECT * FROM really_big_query

For an experiment, try to change the proc to not actually return anything (i.e. comment out that select statement). Does odbc_num_rows() still return over 12k rows? — Ruslan, Feb 13 '14 at 23:04
Try adding `set nocount on;` at the top of the block of SQL statements. Sql returns a count of the number of rows effected by each command back to the client - which causes these empty result sets. — dav1dsm1th, Feb 15 '14 at 08:57
@dav1dsm1th - Good call, I overlooked that. It has fixed the issue. Can you post it as an answer so I can accept it? — Cory Dee, Mar 11 '14 at 17:03

score 1 · Accepted Answer · edited May 23 '17 at 12:05

Generally speaking it's always a good idea to start every stored procedure, or batch of commands to be executed, with the set nocount on instruction - which tells SQL Server to supress sending "rows effected" messages to a client application over ODBC (or ADO, etc.). These messages effect performance and cause empty record sets to be created in the result set - which cause additional effort for application developers.

I've also used ODBC drivers that actually error if you forget to suppress these messages - so it has become instinctive for me to type set nocount on as soon as I start writing any new stored procedure.

There are various question/answers relating to this subject, for example What are the advantages and disadvantages of turning NOCOUNT off in SQL Server queries? and SET NOCOUNT ON usage which cover many other aspects of this command.

ODBC, PHP and SQL Variables

1 Answers1