March 22, 2010 at 1:36 am
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
March 22, 2010 at 2:14 am
have a look at @@rowcount in BOL (Books On Line) http://msdn.microsoft.com/en-us/library/ms187316.aspx
March 22, 2010 at 12:13 pm
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
March 22, 2010 at 1:19 pm
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
March 23, 2010 at 4:16 am
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
March 23, 2010 at 6:44 am
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
March 23, 2010 at 7:35 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply