Implement Paging With SQL Server 2008 in MVC3 C#

Question

Currently I have a frontend JQuery datatable (which is set to serverside), and my stored procedure (SP) is returning all of the data as below:

Select ID, FullName, FullAddress, Coord
From Names n
inner join Person p ON n.ID = p.PID
inner join Address a ON n.AddressID = a.ID
order by FullName

I want to achieve paging and restrict to only 200 rows of data at a time. I can pass in the page no (page no begins from 0) as parameter to SP i.e. for example if the pageno = 0 the first 200 rows should only be returned. if page no = 1 rows from position 200 to 400 will be returned and so on.

How can I change the above SP to achieve this ?

http://stackoverflow.com/a/2135449/1524085 – Pedro Estrada Feb 12 '14 at 13:41 — Pedro Estrada, Feb 12 '14 at 13:41

Sai Avinash · Accepted Answer · 2014-02-12T13:48:15.820

If you want to retrieve data based on each page request , you can use the following stored proc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

CREATE PROCEDURE GetDataPageWise                      // Name of the stored procedure
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;

Select Row_Number() Over
(
 ID
) as RowNumber
ID, FullName, FullAddress, Coord
into #Results// #Results is the temporary table that we are creating
From Names n
inner join Person p ON n.ID = p.PID
inner join Address a ON n.AddressID = a.ID
order by FullName
SELECT @RecordCount = COUNT(*)FROM #Results

SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

      DROP TABLE #Results                      // Dropping the temporary table results as it is not required furthur
END
GO

Here, you need to send the parameters current page index that you are requesting , page size that is number of records per page

Hope this helps..

Avinash can you modify the above to include my Select statement and the joins from my question above. thanks — user2906420, Feb 12 '14 at 13:40

Implement Paging With SQL Server 2008 in MVC3 C#

1 Answers1