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: 2 days ago @ 1:33 AM
Points: 1,949, Visits: 8,310
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, 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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 21,631, Visits: 15,291
Nice sample and explanation Dave and Bob.



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


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
Posted Tuesday, March 23, 2010 4:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 11,194, Visits: 11,137
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, 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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 11,194, Visits: 11,137
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