Include the number of row returned

• Michael Thomsen

  Grasshopper

  Points: 17

  More actions

  June 15, 2010 at 1:23 am

  #225610

  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