Pagging Index in Stored Procedure

Mohammedjuned Ansari

8y850Learn iOS Programming

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.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravi Patel8y ago
Hi,

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

I think OFFSET Keyword does not support bellow SQL Server 2012
Mohammedjuned Ansari8y ago
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
Mohammedjuned Ansari8y ago
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.
Bharathi Raja8y ago
http://www.c-sharpcorner.com/blogs/efficient-paging-using-stored-procedure-in-sql-server
Ravi Patel8y ago
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
Suraj Kumar8y ago
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