Help with @@rowcount - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with @@rowcount

Author

Topic

writhe
Starting Member

15 Posts

Posted - 2002-05-16 : 18:28:16

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

AS
	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

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-05-16 : 18:42:23

First thing is to put SET NOCOUNT ON at the top. Instead of doing a SELECT *, do a select on a specific field and assign a variable to it like so:

SET NOCOUNT ON
DECLARE @today INT, @unprocessed INT, @total INT,@lintTemp INT

SELECT @lintTemp=somefield FROM orders WHERE TransTime > CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10))
SET @today = @@rowcount

SELECT @lintTemp=somefield FROM orders WHERE TransactionID in (SELECT TransactionID FROM orderstatus WHERE Status = 0)
SET @unprocessed = @@rowcount

SELECT @lintTemp=somefield FROM orders
SET @total = @@rowcount

SELECT @today AS TodaysOrders, @unprocessed AS UnprocessedOrders, @total AS TotalOrders

*************************
Someone done told you wrong!

writhe
Starting Member

15 Posts

Posted - 2002-05-16 : 19:30:27

Roy-

I worked around the problem by making the variables output parameters, which I then accessed with ADO in the ASP page.

Thanks for your help.

dsdeming

479 Posts

Posted - 2002-05-17 : 08:12:01

Why even select the data in the first place? Why not just select the counts?

SELECT @today = COUNT( * ) FROM orders WHERE TransTime > CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10))

SELECT @unprocessed = COUNT( * ) FROM orders WHERE TransactionID in (SELECT TransactionID FROM orderstatus WHERE Status = 0)

SELECT @total = COUNT( * ) FROM orders

SELECT @today AS TodaysOrders, @unprocessed AS UnprocessedOrders, @total AS TotalOrders

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources