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
@totalCount int OUT
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.