分頁查詢存儲過程sql語句

--建立分頁的存儲過程

alter procedure proc_page

@tabName varchar(50),

@pageIndex int=1,

@pageSize int=5,

@pk varchar(20)='id',

@cols varchar(200)='*',

@where varchar(500)='1=1',

@order varchar(50)='',

@count int output,

@pageCount int output

as

declare @sql varchar(1000)

if(@order='')

    set @[email protected]

set @sql='select top '+CONVERT(varchar(20),@pageSize)+' '[email protected]+' from '[email protected]+' where '[email protected]

    +' not in (select top '+CONVERT(varchar(20),(@pageIndex-1)*@pageSize)+' '[email protected]+' from '[email protected]

    +' where '[email protected]+' order by '[email protected]+') and '[email protected]+' order by '[email protected]

print @sql

exec sp_sqlexec @sql

exec proc_page @tabName='userInfo'