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

Include the number of row returned Expand / Collapse
Author
Message
Posted Tuesday, June 15, 2010 1:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 3:30 AM
Points: 1, Visits: 3
Nice little procedure, I have it in just another version:
Really often you need to know if its the last page in the data, and yes you can have .Net to ask on the DataSet.
Here is a simple return that gives the number of rows returned.

test:
declare @i as int
exec @i=dbo.usp_ShowBooks 1,230
select @i as numofrows

Here it is worked in the Lawrence version
create PROCEDURE dbo.usp_ShowBooks
@PageNumber INT,
@PageSize INT
AS
BEGIN
set nocount on
;WITH BookCTE (RowNumber, BookAuthor, BookTitle, BookEdition)
AS
(
SELECT
ROW_NUMBER()OVER (ORDER BY BookAuthor , BookTitle, BookEdition),
BookAuthor, BookTitle, BookEdition
FROM dbo.Books
)
SELECT TOP (@PageSize) RowNumber, BookAuthor, BookTitle, BookEdition
FROM BookCTE WHERE RowNumber>((@PageNumber-1)*@PageSize)
ORDER BY BookAuthor, BookTitle, BookEdition
return @@rowcount
END

Post #937283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse