CODEWITHSUNDEEP
phpsqlt-sqlodbc
Cory Dee
Cory Dee

Reputation: 2891

ODBC, PHP and SQL Variables

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

Upvotes: 2

Views: 764

Answers (1)

db9dreamer
db9dreamer

Reputation: 1715

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.

Upvotes: 1

Related Questions