I Need Help with @@rowcount
I'm trying to write a stored procedure that can gather some database statistics. I'm running SQL Server 2000 and am trying to take advantage of @@rowcount, which I've read is better on performance than COUNT(). Below, you'll see I've used several SELECT statements to fill three variables with the @@rowcount function. My problem is that I only want to return one recordset ... the one containing "TodaysOrders," "UnprocessedOrders," "TotalOrders" so I can access them in an ASP page. Is it possible to drop the prior SELECT statements or somehow create output variables?
CREATE PROCEDURE dbo.sp_CountRecords
DECLARE @today INT, @unprocessed INT, @total INT
SELECT * FROM orders WHERE TransTime > CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10))
SET @today = @@rowcount
SELECT * FROM orders WHERE TransactionID in (SELECT TransactionID FROM orderstatus WHERE Status = 0)
SET @unprocessed = @@rowcount
SELECT * FROM orders
SET @total = @@rowcount
SELECT @today AS TodaysOrders, @unprocessed AS UnprocessedOrders, @total AS TotalOrders
i never said i knew what i was doing
Well, I'm no pro at this. Maybe I should change the SELECT statements to select a single column, rather than all of them and isn't there a statement that tells SQL to not create a recordset?
Anyway, this is an excerpt from Beginning SQL Programming, Wrox Press (p. 371):
Sorry, it's not more detailed but that's all I've got for you.
Performance Tip: The COUNT function, as well as other Aggregate functions, is not particularly easy on performance. If we want to get the count of records returned by a query with SQL Server, we can access the @@ROWCOUNT variable, which always contains the number of rows affected by the last query of the connection.