stored procedure with paging sql server

Introduction:

Here I will explain how to create stored procedure with paging in SQL server. In the database thousand of records and we fetch some record from database record that time required to create stored procedure with paging in SQL server. If we want to get record from table for paging then required to pass Page index and page site as input parameter in stored procedure.

So, Now I explaining how to create stored procedure with paging SQL server

Description: 

In previously post I explained to  
Three tier architecture in asp.net
Remove .aspx from url in asp.net
Temp table in sql
Change the column name or datatype or size in sql server
Connectionstring in asp.net
Calling web service without adding web reference 



stored procedure with paging SQL server
custom paging stored procedure SQL server 2008
implement paging in SQL stored procedure
implementing pagination in stored procedure
paging in stored procedure SQL server 2012
stored procedure with sorting, paging, and filteringSQL stored procedure paging row_number
custom paging in gridview using stored procedure
custom paging in SQL server 2008

how to create stored procedure with paging and sorting SQL server

 Example:

CREATE PROCEDURE GetUserList

(         

  @SortOrder VARCHAR(300)=NULL,     

  @PageIndex INT=NULL,                 

  @PageSize INT=NULL        

)         

AS         

BEGIN         

DECLARE @SQLQuery NVARCHAR(6000)         

IF (@SortOrder='' OR @SortOrder IS NULL)       

       SET @SortOrder='FirstName asc';

      

SET @SQLQuery = 'WITH PAGED AS

                           (SELECT

                           row_number() OVER( ORDER BY '+@SortOrder+') AS RowNumber ,

                           ID,

                           FirstName,

                           LastName

                           FROM tblUserDetails U

                           WHERE IsActive=1'

SET @SQLQuery = @SQLQuery + '

                           SELECT *, (SELECT COUNT(*) FROM PAGED) AS TotalRecordCount 

                           FROM PAGED               

                           WHERE 

                           PAGED.RowNumber BETWEEN (' + CONVERT(NVARCHAR(10), @PageIndex) + ' - 1) *

                           ' + CONVERT(NVARCHAR(10), @PageSize) + ' + 1 AND

                           ' + CONVERT(NVARCHAR(10), @PageIndex) + ' *

                           ' + CONVERT(NVARCHAR(10), @PageSize) + ' '                    

        

PRINT @SQLQuery     

EXECUTE(@SQLQuery)     

END

Now execute above stored procedure oin your SQL to create SP with paging and sorting in SQL server (change field name and table name which you want to paging).

And now execute this command 

EXEC GetUserList '',1,5

EXEC GetUserList '',10,10

EXEC GetUserList 'LastName DESC',20,5

 
Above command you will get result like 
first 5 records for first page
and second command will get 10 records of 10 th page
and so on.

You can use this stored procedure to bind gridview with custom paging.