Include the number of row returned

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lawrence Moore » Include the number of row returned

Include the number of row returned

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Thomsen

Posted Tuesday, June 15, 2010 1:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 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 »

Permissions