How should i display first 10 records at a time in sql (paging in SQL)?

To fetch rows in Sql server(10/5 rows at a time) to apply pagination or paging in SQL, you can use OFFSET and FETCH (For SQL server 2012 and above), here is the example, I am using AdventureWorks2012 Database

Fetch First 10 rows of Table Person

USE AdventureWorks2012
GO
SELECT 
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 0 ROWS 
  FETCH NEXT 10 ROWS ONLY
GO

Here is the output image

Now, if we want to get next page (next 10 rows), you can give OffSet value=10 to start fetching rows from 11th and use Fetch Next 10 to get another 10 rows, here is the query.

USE AdventureWorks2012
GO
SELECT 
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 10 ROWS 
  FETCH NEXT 10 ROWS ONLY
GO

Output:

You can do this again and again to fetch next 10 rows and so on.

You can create the Stored procedure to use the same above query, here is the stored procedure query code for same

USE AdventureWorks2012
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[ExampleUsageOfSQLServerPagingFeature]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ExampleUsageOfSQLServerPagingFeature]
GO
CREATE PROCEDURE ExampleUsageOfSQLServerPagingFeature
 (
  @PageNo INT,
 @RowCountPerPage INT
 )
AS
SELECT
  BusinessEntityID
 ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID
  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
  FETCH NEXT @RowCountPerPage ROWS ONLY
GO

Simply execute the above created stored procedure by passing page number and row count to show

/* Display Records Between 11 AND 20 BusinessEntityID */
EXECUTE ExampleUsageOfSQLServerPagingFeature 2, 10
GO

Output:

For showing this in view, you can just call the stored procedure and get the list of next 10 rows based on page number and show data in view

Refer this article https://qawithexperts.com/article/asp.net/paging-sorting-and-filtering-in-aspnet-mvc-c/31

for more details on how to implement paging in MVC View.

 This is quite basics to call stored procedures using controller C#, if you want to directly call the SP, I have already mentioned it in the above answer you can use the this query

EXECUTE ExampleUsageOfSQLServerPagingFeature 2, 10

Now, Suppose you are using Entity framework(database first approach in my example) in your controller, code can be as below

 public ActionResult ListToShow(int? page)
        {
            var Model = new List<ExampleUsageOfSQLServerPagingFeature_Result>();
            int pageNumber = (page ?? 1);
           
          
            using (var context = new AdventureWorks2012Entities1())
            {
                //show results 10 per page
                var CountToShowPerPage = 10;
                //Execute stored procedure as a function
                Model = context.ExampleUsageOfSQLServerPagingFeature(pageNumber, CountToShowPerPage).ToList();

                //Get Total numbers for row count, for example this example let's say it is 100
                ViewBag.TotalCount = 100;

                //Save Current page number
                ViewBag.CurrentPage = pageNumber;
            }
           return View(Model);
        }

In your View, get the list and display it in table (I am using Custom paging, there are several other options Like IPagesList.MVC for which I have already asked you to refer https://qawithexperts.com/article/asp.net/paging-sorting-and-filtering-in-aspnet-mvc-c/31)

Razor Code

@model IEnumerable<PagingWithStoredProcedure.Models.ExampleUsageOfSQLServerPagingFeature_Result>
@{
    ViewBag.Title = "List To Show";
}

<h2>List To Show</h2>

<table class="table table-condensed">
    <thead>
        <tr>
            <th> Id</th>
            <th>Type</th>
            <th>Full Name</th>
        </tr>

    </thead>
    <tbody id="tBody">
        @foreach (var row in Model)
        {
            <tr>
                <td>
                    @row.BusinessEntityID
                </td>
                <td>
                    @row.PersonType
                </td>
                <td>
                    @row.FullName
                </td>
            </tr>
        }
    </tbody>
</table>
<ul class="pagination">
    @for (var i = 1; i < (ViewBag.TotalCount/10); i++)
    {
        //if current page add active class
        if (i == ViewBag.CurrentPage)
        {
            <li class="active"><a href="javascript:void(0)">@i</a></li>
        }
        else
        {
            <li><a href="/Home/ListToShow?page=@i">@i</a></li>
        }


    }
</ul> 

Now, after testing this locally, in my PC, here is the output

Remember, you would have to change code according to your need.

In this article https://qawithexperts.com/article/asp-net/bind-data-to-gridview-using-jquery-ajax-webmethod-in-aspnet/125 which is related to Bind Data to Gridview using jQuery Ajax & WebMethod in ASP.NET, above SQL stored procedure paging is explained with custom javascript to render pages in View, you can use this example for reference also, to understand how to implement paging in SQL server stored procedure.