Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Pagination stored procedure in SQL Server

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.64k
    Comment on it

    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.

           @startRowIndex int,
           @pageSize int,
           @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.



 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: