Pagging Index in Stored Procedure

Question

how to create pagging in stored procedure and a page size and page index intered by the user. for example: if i want to only page 1 and Customer only display 1 to 10 then again i was changed then it will be change.

Mohammedjuned Ansari · Answer

Hi Suraj, You was not under stand my Question http://www.dotnetawesome.com/2014/02/how-to-implement-custom-paging-in-gridview-using-stored-procedure.html no use any asp.net control to pagging

Bharathi Raja · Answer

http://www.c-sharpcorner.com/blogs/efficient-paging-using-stored-procedure-in-sql-server

Ravi Patel · Answer

Hi, OFFSET and FETCH is introdused in SQL Server 2012. so this feature will support SQL Server 2012. and onward version

Ravi Patel · Answer

Hi, I think OFFSET Keyword does not support bellow SQL Server 2012

Mohammedjuned Ansari · Answer

Hi Ravi, i was writing the same code in stored procedure but errors occurred CREATE PROCEDURE Pagging @NoOfRow INT=10, @pageindex INT =1 AS BEGIN SELECT * FROM Sub_Category_Master ORDER BY sub_category_id OFFSET @NoOfRow *(@pageindex-1)Rows Fetch Next @NoOfRow ROWS ONLY OPTION (RECOMPILE) END GO Errors: Incorrect Syntex near OFFSET ,@pageindex,ROWS and RECOMPILE.

Suraj Kumar · Answer

Hello, You can implement this simple example and get the result http://www.dotnetawesome.com/2014/02/how-to-implement-custom-paging-in-gridview-using-stored-procedure.html Thanks

Ravi Patel · Answer

Hi , create stored procedure Like this CREATE PROCEDURE [dbo].[GetCountryList] ( @NoOfRow INT=10, @pageindex INT =1 ) AS BEGIN SELECT * FROM Country ORDER BY countryid OFFSET @NoOfRow * (@pageindex - 1) ROWS FETCH NEXT @NoOfRow ROWS ONLY OPTION (RECOMPILE) END By deafualt pageindex should be 1 if you face any issue let me know i will help you