Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Count the rows SQL Server 2005
Add to briefcase

Count the rows SQL Server 2005 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
WangcChiKaBastar
Posted Monday, March 22, 2010 1:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 2:23 PM
Points: 73, Visits: 334
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
Post #887195
Dave Ballantyne
Posted Monday, March 22, 2010 2:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227
have a look at @@rowcount in BOL (Books On Line) http://msdn.microsoft.com/en-us/library/ms187316.aspx


Clear Sky SQL
My Blog
Kent user group
Post #887206
The Dixie Flatline
Posted Monday, March 22, 2010 12:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538
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? -- Stephen Stills
Post #887576
SQLRNNR
Posted Monday, March 22, 2010 1:19 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733, Visits: 12,330
Nice sample and explanation Dave and Bob.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #887644
Paul White
Posted Tuesday, March 23, 2010 4:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #887947
The Dixie Flatline
Posted Tuesday, March 23, 2010 6:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538
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? -- Stephen Stills
Post #888058
Paul White
Posted Tuesday, March 23, 2010 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529
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


Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #888118
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.