Create stored procedure for pagination large records

Today I will show you how you can create stored procedure for pagination.

Create Stored Procedure

So lets start with Stored procedure creation. open Sql Server management studio and select your desired database which you are planning to use for this exercise. Expand Database node

  • Move to Programmability
  • Stored Procedures
Right Click on Stored Procedures node and select Create a new stored procedure

Stored Procedures in Entity framework

Stored procedure Code

  CREATE PROCEDURE UspFetchEmployeeRecords 
  @PageNumber INT = 1,
  @PageSize   INT = 100
AS
BEGIN
  SET NOCOUNT ON;
 
 select emp_id,fname+' '+lname as EmployeeName,hire_date
 from employee
 ORDER BY emp_id
 OFFSET @PageSize * (@PageNumber - 1) ROWS
 FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);

END
GO
        

After Stored Procedure creation Hit F5 to execute this. you will see success message in Message window.

Description

So you can test this application by passing Page Number and Page Size parameters. You can also learn how you can use Stored procedure in your Asp.net MVC Application with Entity framework.