Getting row count from stored procedure

How can I return the row count from a stored procedure?  This seems like a very simple thing, as the result set should know how many rows it contains, but I can't figure it out.

Basically, I have a lengthy parameterized stored procedure and I'd like to know the row count (possibly with an output parameter) without having to run a Count(*) query or looping through the record set with a counter.

Thanks in advance.
LVL 1
Krash_ioAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:

put the following line after the insert/update operation, whose count u need

select @@rowcount
0
Krash_ioAuthor Commented:
Wow, I barely had the browser closed before you replied.  That works perfectly, even though I have a parameterized  SET ROWCOUNT command which is limiting the records to 10-50, it returns how many rows would be returned if the row count wasn't set.

I'll award you the points, but do you know how I can get that in an output parameter.  That way I don't have an extra column for 2,000 rows just for the row count?
0
Aneesh RetnakaranDatabase AdministratorCommented:
You can use output params also

create proc urSp
...
@RowCount int output
BEGIN
         ur insert /Update operattion
         @rowCount  = @@ROWCOUNT
END
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Krash_ioAuthor Commented:
Oh, sorry, I messed up.  I had a line of code one line too high, and I tested your soultion incorrectly.

That soultion doesn't appear to work when limiting the results with the SET ROWCOUNT comand.  I want to limit the number of rows returned, but I want to know the total number that would be returned were the rowcount never set.  I forgot that I was using the SET ROWCOUNT command when I first posted this.

Is there any way to do that?  I have a feeling there's not....
0
Aneesh RetnakaranDatabase AdministratorCommented:



inside your sp, u need to use dynamic sql

declare @sql nvarchar(1000)
set @sql = 'select top '+cast(@rowcount as varchar(10))+ ' * FRom yourTable '

exec sp_executeSQL @sql

0
Krash_ioAuthor Commented:
Sorry, I might have to up the points for this.

I tried the following as a test:

CREATE PROCEDURE test
{
     declare @sql nvarchar(1000)
}
as
begin

set rowcount 10
set @sql = 'select top ' + cast(@rowcount as varchar(10)) +  ' * FRom Plans;'
exec sp_executeSQL @sql

END

I'm setting the row count to make sure the @@rowcount global variable will still return the total number of records that would have been returned if hte set rowcount command were not used.

I'm getting an "Error 0: Syntax error or access violation"
0
Anthony PerkinsCommented:
>>Is there any way to do that? <<
Not directly.  But you can do a COUNT(*) without the SET ROWCOUNT and then do the SELECT with the SET ROWCOUNT.  The way we do it is a little different.  Since we are doing paging we insert all the rows into a variable of type table (or temporary table) which has an IDENTITY column. This gives us the total number of rows. We then select only the rows required.

CAVEAT:  In order to read the Output parameter you must close the recordset prior to reading the output parameter.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>I'm getting an "Error 0: Syntax error or access violation"<<
You are missing a comma.  But there is no need to resort to Dynamic SQL to get the total number of rows.
0
Aneesh RetnakaranDatabase AdministratorCommented:
0
Krash_ioAuthor Commented:
Ok, I should explain a little bit more.

I want the total rows that would be returned were the ROWCOUNT not set because I'm making a paging routine.  I actually saw that link you sent aneeshattingal when I was searching for paging patterns.  It doesn't work with my solution though, or at least I can't get it to work.  I've got a 100 line, approx. 5,000 character parameterized stored procedure.  It's used for a search interface on a website.

My paging routine is very simple.  Every record has an integer primary key.  Simply select 10 records where the primary key is greater than the primary key of the last record returned on the previous page.  Since every page will execute the stored procedure in the exact same way, with the exact same parameters, and in the exact same order, with the only parameter that changes being the @LastRowPK parameter, I can select the next 10 records in order for output on the website’s search results.

Right now I'm getting the total row count by first running the parameterized query with a simple Count(*) AS TotalRows select statement.  I pass it all of the parameters the second query uses, which I run next to get the actual data.
 
I don't like this though because I have to row two queries and it's too slow.

I really don't want to use dynamic SQL, I've did a lot of work to avoid that.  However, I'm sorry I don't understand how to implement the dynamic SQL solution you recommended aneeshattingal.

Is there a way to get the total rows that would be returned were the ROWCOUNT not set?
0
Anthony PerkinsCommented:
>>Is there a way to get the total rows that would be returned were the ROWCOUNT not set?<<
No.  Did you not see my comments?
0
Aneesh RetnakaranDatabase AdministratorCommented:
>Is there a way to get the total rows that would be returned were the ROWCOUNT not set?

I agree with Aceperkins;

your sp should be like this


Create procedure ..
AS
BEGIN

           SELECT @Count = COUNT(*) FROM urTable
           
           SET ROWCOUNT 10

           --ur Select Statement here

END
0
Krash_ioAuthor Commented:
Alright, that's what I was afraid of.  I've increased the points and split them.  aneeshattingal did give me a correct answer to my original post, however I fogot to mention I was using the SET ROWCOUNT command.  acperkins gave me the final answer.  I hope this is fair.
0
Anthony PerkinsCommented:
Fair enough.

Once again and in case you missed it:

CAVEAT:  In order to read the Output parameter you must close the recordset prior to reading the output parameter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.