To get the paginated data from stored procedure, we need to pass at least three parameters if the paginated data doesn’t need any filtration.
startRowIndex– start fetching records from which row number
pageSize– number of records to fetch
totalCount– total count of records into the database table that will be used to render the page number links on the web page so that user will be able to navigate to different pages.
CREATE PROCEDURE GetPersonalDetailsPaginated -- Add the parameters for the stored procedure here @startRowIndex int, @pageSize int, @totalCount int OUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Get the paginated records SELECT PersonalDetailsId, FirstName, LastName, Age, Active FROM PersonalDetails -- keep WHERE clause here if any ORDER BY PersonalDetailsId OFFSET @startRowindex ROWS FETCH NEXT @pageSize ROWS ONLY -- get the total count of the records SELECT @totalCount = COUNT(PersonalDetailsId) FROM PersonalDetails -- keep the same WHERE clause as above
Note that we have @startRowIndex and @pageSize as normal parameters (input parameter) and @totalCount as OUT (output parameter) whose value should be set inside the stored procedure.
Write above SQL code into the query window and execute it that will create the stored procedure. In this stored procedure, we are first selecting PersonalDetails records ordered by PersonalDetailsId
and then using
OFFSET (skip following number of records) the value of @startRowIndex and the
FETCH NEXT @pageSize rows from the PersonalDetials table.
The next SELECT statement is setting the value of the @totalCount.
Now when we Execute Stored procedure by right clicking the stored procedure name, we see that the first result set shows as the paginated record and the second result as the totalCount value that is nothing but the total count of the records in the PersonalDetails database table.
Views: 3476 | Post Order: 94