sql通用多表分页存储过程

--exec upGetPagerInfo @itemsPerPage=10,@currentPageIndex=12,@selectCommand='select * from [user]',
@sortField='createtime',@OrderType=0,@idField='ID'

ALTER  PROCEDURE [dbo].[upGetPagerInfo]
@itemsPerPage int=0,      --页尺寸
@currentPageIndex int=1,  --页码
@selectCommand varchar(4000), --查寻语句
@sortField varchar(100)='',--排序的字段名
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序
@idField varchar(50)='',--根据分页的字段,为主键
@dataType int=1
AS
set nocount on
declare @strSQL   varchar(5000)      -- 主语句 
declare @strTmp   varchar(110)        -- 临时变量 
declare @strOrder varchar(400)        -- 排序类型 
declare @forwardPageCount int       --以浏览的记录总数
 

if @OrderType != 0 
 
begin 
 
  -- set @strTmp = '<(select min' 
 
set @strOrder = ' order by ['+ @sortField +'] desc'
 
--如果@OrderType不是0，就执行降序，这句很重要！ 
 
end 
 
else 
 
begin 
 
   --set @strTmp = '>(select max'
 
   set @strOrder = ' order by ['+ @sortField +'] asc'
 
end 
if @dataType=0
 exec('select count(*) from ('+@selectCommand+' ) as t0')
 --记录总数
else if @dataType=1
BEGIN
 if @currentPageIndex=1
    begin
 
 set @forwardPageCount=@itemsPerPage * (@currentPageIndex-1)
 
 exec('select top '+@itemsPerPage+' * from ('+@selectCommand+' ) as t0 '+@strOrder )
 
 --如果是第一页就执行以上代码，这样会加快执行速度 
 
    end
   else
   begin
 set @forwardPageCount=@itemsPerPage * (@currentPageIndex-1)                              -->(select max(@idField)from (select top '+@forwardPageCount+' '+@idField+' from ('+@selectCommand+') as t0 order by '+@sortField+' ) order by '+@sortField)
 --exec('select top '+@itemsPerPage+' * from ('+@selectCommand+' ) as t0 where '+@idField+' not in (select top '+@forwardPageCount+' '+@idField+' from ('+@selectCommand+') as t0 order by '+@sortField+' ) order by '+@sortField)
 exec('select top '+@itemsPerPage+' * from ('+@selectCommand+' ) as t0 where '+@idField+' not in (select top '+@forwardPageCount+' '+@idField+' from ('+@selectCommand+') as t0  '+@strOrder+' )  '+@strOrder)
   end
END
else
BEGIN
   if @currentPageIndex=1
   begin
   set @forwardPageCount=@itemsPerPage * (@currentPageIndex-1) 
   exec('select top '+@itemsPerPage+' * from ('+@selectCommand+' ) as t0 '+@strOrder )
   end
   else
   begin
    set @forwardPageCount=@itemsPerPage*(@currentPageIndex-1)
    exec('select top '+@itemsPerPage+' * from ('+@selectCommand+' ) as t0 where '+@idField+'<(select min(id) from (select top '+@forwardPageCount+' '+@idField+' from ('+@selectCommand+') as t0 '+@strOrder+' )t1)  '+@strOrder) 
   end
   
END
set nocount off