records + record count in 1 query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

records + record count in 1 query?

Author

Topic

Donny Bahama
Starting Member

13 Posts

Posted - 2006-09-15 : 12:52:22

When I need a recordset + a count of the records, I've been using two queries, which is a nuisance. A select query containing Count(*) as RecCount and one or more fields returns an error telling me to use Group By. But using Group By returns a count of 1 for each record returned. Is there a way to do this in a single query?

---------------------------------------------------------------
I'm the guy who's not afraid to ask the painfully stupid questions!

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-15 : 12:55:34

SELECT....
SET @rowcount = @@ROWCOUNT

pass the variable as an OUTPUT Parameter out of a SPROC

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Donny Bahama
Starting Member

13 Posts

Posted - 2006-09-15 : 13:08:43

Thanks. I just figured out that this also works:

select sq.CustCount, c.FirstName, c.LastName 
from customers c, (select count(*) as CustCount from Customers) sq

---------------------------------------------------------------
I'm the guy who's not afraid to ask the painfully stupid questions!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 13:40:00

quote:
Originally posted by Donny Bahama

Thanks. I just figured out that this also works:
select sq.CustCount, c.FirstName, c.LastName
from customers c, (select count(*) as CustCount from Customers) sq

Perhaps it works for you, however it is inefficient. You should be using @@ROWCOUNT to get the count of the previous statement. You could then output this value from the stored procedure to your application. So you would call the stored procedure once, but it would contain everything that you need.

CREATE PROC SomeProc
(@Cnt int OUTPUT)
AS

SELECT FirstName, LastName
FROM Customers

SET @Cnt = @@ROWCOUNT
GO

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-15 : 15:00:16

quote:
Originally posted by tkizer

quote:
Originally posted by Donny Bahama

Thanks. I just figured out that this also works:
select sq.CustCount, c.FirstName, c.LastName
from customers c, (select count(*) as CustCount from Customers) sq

Perhaps it works for you, however it is inefficient. You should be using @@ROWCOUNT to get the count of the previous statement. You could then output this value from the stored procedure to your application. So you would call the stored procedure once, but it would contain everything that you need.

CREATE PROC SomeProc
(@Cnt int OUTPUT)
AS

SELECT FirstName, LastName
FROM Customers

SET @Cnt = @@ROWCOUNT
GO

Tara Kizer

that sounds oddly familiar for some reason

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 15:22:11

Well it should sound familiar, you suggested it! I just wanted to provide a little more detail so that the poster could possibly implement something like this.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources