Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Getting row count from stored procedure

Posted on 2006-04-19
14
Medium Priority
?
866 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Krash_io
  • 5
  • 5
  • 4
14 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 16494328

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

select @@rowcount
0
 
LVL 1

Author Comment

by:Krash_io
ID: 16494376
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16494409
You can use output params also

create proc urSp
...
@RowCount int output
BEGIN
         ur insert /Update operattion
         @rowCount  = @@ROWCOUNT
END
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:Krash_io
ID: 16494420
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16494503



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
 
LVL 1

Author Comment

by:Krash_io
ID: 16494571
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 16494587
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16494590
>>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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16494599
0
 
LVL 1

Author Comment

by:Krash_io
ID: 16494713
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16494748
>>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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16495025
>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
 
LVL 1

Author Comment

by:Krash_io
ID: 16495052
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16503644
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question