Count the rows SQL Server 2005

Question

Post reply

Count the rows SQL Server 2005

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
March 22, 2010 at 1:36 am

#220171

I have to write a sp that would give two data sets one with the coumns (metadata) like firstname, lastname, address, phonenumber
and then second result set would return just the count of the rows
count(*) of the above select
please advise

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

have a look at @@rowcount in BOL (Books On Line) http://msdn.microsoft.com/en-us/library/ms187316.aspx

Clear Sky SQL
My Blog[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Dave's right. Here's a good practice tip:

@@ROWCOUNT changes with each new SET or SELECT, so if you want to hold on to the value for a while, be sure to store it in the variable of your choice.

-- always returns a @@ROWCOUNT of 1, because of the SET statement

declare @DoNothing int

select name from sys.databases

SET @DoNothing = 1

select @@ROWCOUNT as rowsReturned

go

-- saves the rowcount in @rc for later reference

declare @rc int

declare @DoNothing int

select name from sys.databases

SET @rc = @@ROWCOUNT -- save for later

set @DoNothing = 1

select @rc as rowsReturned

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Nice sample and explanation Dave and Bob.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 4

WangcChiKaBastar (3/22/2010)
I have to write a sp that would give two data sets...

It is usually more efficient to just return one data set, and return the row count in an output parameter.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

True, but then you create headaches for the poor, struggling UI developer who has to learn how to handle two different types of output from a single procedure. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 6

The Dixie Flatline (3/23/2010)
True, but then you create headaches for the poor, struggling UI developer who has to learn how to handle two different types of output from a single procedure. 😉

In my experience, UI developers are happier* to learn about OUTPUT parameters than to use MARS.

Turning a parameter into an OUTPUT parameter generally involves just specifying its direction in an existing .NET statement.

* = also, if they want to use my database, they will do it my way :laugh: :laugh: :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi