Thursday, 12 September 2013

custom paging using stored procedure sql server

/* create table */
create table tblCustomPaging (StudentName varchar(100),MobileNo varchar(20))

/* Insert into table */
insert into tblCustomPaging values('Amit','9876543210')
insert into tblCustomPaging values('Abhishek','8567575700')
insert into tblCustomPaging values('Sumit','7456456460')
insert into tblCustomPaging values('Bhuppy','9567575788')
insert into tblCustomPaging values('CP','8867575788')
insert into tblCustomPaging values('Sandeep','7767575788')
insert into tblCustomPaging values('OP','9967575788')
insert into tblCustomPaging values('Tarun','8867575700')
insert into tblCustomPaging values('Sachin','8867575700')
insert into tblCustomPaging values('Rajeev','9876543210')
insert into tblCustomPaging values('TK','9876541111')
insert into tblCustomPaging values('Mukesh','9876522222')

Method (1)

; With tmpResult As ( SELECT Row_Number() Over (ORDER BY StudentName) as RowNumber,
 StudentName, MobileNo from tblCustomPaging  as a )
 SELECT a.*, b.MaxRecords FROM tmpResult a CROSS JOIN
    (SELECT count(*) as MaxRecords FROM tmpResult) b
    WHERE RowNumber >  ((2 - 1) * 5)  and RowNumber <= (2 * 5) ORDER BY 1

Method (2)

/* create store procedure */

create proc USP_CustomPagin
@pageSize int=5,   
@pageNumber int=1 ,
@criteria varchar(max)='' 
as   
declare @endRowNumber int   
declare @startRowNumber int   
set @endRowNumber = @pageSize * @pageNumber   
set @startRowNumber  = (@endRowNumber - @pageSize)+ 1   
   
exec('select top '+ @pageSize +' Sno,totalCount, StudentName, MobileNo from (   
        select (select count(*) from tblCustomPaging where 0=0 '+  @criteria + ') as totalCount,   
        row_number() over(order by StudentName)Sno, StudentName, MobileNo   
        FROM tblCustomPaging  where 0=0 '+  @criteria + ' )as d   
            where d.sno between '+ @startRowNumber +' and  '+ @endRowNumber )


/* Run below query like */
USP_CustomPagin
USP_CustomPagin 5,1
USP_CustomPagin 5,2
USP_CustomPagin 5,1,' and StudentName Like ''%a%'' '
USP_CustomPagin 5,1,' and StudentName Like ''%a%'' OR MobileNo like ''%88%'''

No comments:

Post a Comment