Question on @@ROWCOUNT

[Geoka]

I have a question on the scope of @@ROWCOUNT. I have a Select statement used in my Stored Procedure, which is given below. I would like to know whether it will return the desired results all the time or any possibility of showing wrong result on multi user environment.

About my requirement, the total count of the records to be returned along with the HCID field.

SELECT @@RowCount as TotalCount,HCID  FROM prod_SPBILL_SSA_Invoice_Entry WHERE Status <> 'G' and (CancelledStatus ='N' or CancelledStatus is NULL)

 

[bborissov]

That statement is WRONG.
The @@ROWCOUNT system variable is evaluated AFTER the result set is prepared, so you ALWAYS would get wrong result, because that SELECT will give you the value of rows returned from some previous SELECT/UPDATE/DELETE command.
You could try this:

SELECT COUNT(*) as TotalCount,
       HCID
FROM prod_SPBILL_SSA_Invoice_Entry
WHERE Status <> 'G' and
      (CancelledStatus ='N' or CancelledStatus is NULL)
GROUP BY HCID

But if you want to COUNT ALL records returned from THIS select you should use a derived table with the same WHERE clause.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Geoka]

My mistake, I pasted wrong code. Updated code is given below.

SELECT @@rowcount as TotalCount,HCID FROM ( SELECT HCID  FROM prod_SPBILL_SSA_Invoice_Entry WHERE  Status <> 'G' and (CancelledStatus ='N' or CancelledStatus is NULL)       
   
            UNION             
            SELECT HCID FROM PROD_SPBILL_SSA_RGN_INPUT_FILE WHERE  Invoice_Generated='N'     
           AND HCID NOT IN (SELECT HCID FROM prod_SPBILL_SSA_Invoice_Entry where PaymentReceived<>'Y')) A

I can't group it. I want the total record count returned from the query.

 

[bborissov]

Again. Rowcount used taht way always will return WRONG value.
Try this:

DECLARE @test TABLE (Fld1 int)
INSERT INTO @Test VALUES(1)
INSERT INTO @Test VALUES(2)
INSERT INTO @Test VALUES(3)
INSERT INTO @Test VALUES(4)
SELECT @@RowCount, Fld1
FROM (SELECT Fld1 FROM @Test) Tst

And you will see that the first column ALWAYS have a value of 1 (the last insert command)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.