Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count the rows SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, March 22, 2010 1:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 2, 2014 11:14 AM
Points: 103, Visits: 450
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
Posted Monday, March 22, 2010 2:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Posted Monday, March 22, 2010 12:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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
Posted Monday, March 22, 2010 1:19 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:51 PM
Points: 17,845, Visits: 15,797
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
Post #887644
Posted Tuesday, March 23, 2010 4:16 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 9:36 PM
Points: 9,927, Visits: 11,189
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
Posted Tuesday, March 23, 2010 6:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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
Posted Tuesday, March 23, 2010 7:35 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 9:36 PM
Points: 9,927, Visits: 11,189
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