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: Tuesday, June 9, 2015 11:24 AM
Points: 103, Visits: 467
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: Sunday, March 8, 2015 12:52 PM
Points: 1,778, Visits: 8,352
have a look at @@rowcount in BOL (Books On Line) http://msdn.microsoft.com/en-us/library/ms187316.aspx



Clear Sky SQL
My Blog
Post #887206
Posted Monday, March 22, 2010 12:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 31, 2015 1:16 PM
Points: 2,810, Visits: 6,169
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: Yesterday @ 4:56 PM
Points: 18,955, Visits: 17,235
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: Monday, July 27, 2015 4:12 AM
Points: 9,932, Visits: 11,285
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
SQLblog.com
@SQL_Kiwi
Post #887947
Posted Tuesday, March 23, 2010 6:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 31, 2015 1:16 PM
Points: 2,810, Visits: 6,169
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: Monday, July 27, 2015 4:12 AM
Points: 9,932, Visits: 11,285
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #888118
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse