In this blog we illustrate how to get the pagination data from SQL server stored procedure.

To create a such type of a stored procedure, we need to send a minimum three parameters(startRowIndex, pageSize, totalCount).
    1. startRowIndex – It is used to define a row number where the records start fetching.
    2. pageSize – It is used to define a number of records to fetch.
    3. totalCount – It is used to define a total number of records in the table.

See the below code to know how we can create a pagination stored procedure in sql server.

CREATE PROCEDURE GetUserDetails
       @startRowIndex int,
       @pageSize int,
       @totalCount int OUT
AS
BEGIN
       SET NOCOUNT ON;
       SELECT UserId, FirstName, LastName Active FROM User
       ORDER BY UserId
       OFFSET @startRowindex ROWS FETCH NEXT @pageSize ROWS ONLY
       SELECT @totalCount = COUNT(UserId) FROM User

In above code we are using a @startRowIndex and @pageSize as input parameter but @totalCount as output parameter it means that we initialize its value within in the stored procedure. In code, We are using a OFFSET  to skip the number of records from the database table.

To execute this stored procedure use the below code:

declare @total_count int
exec [dbo].[GetUserDetails] 0,4,@total_count output
select @total_count as 'Total Count'

After executing the above code we get a tow results- First result will display the pagination record and second result will display the total number of record inside a User table.