共用分页存储过程

USE [NPU_Organization]
GO
/****** Object:  StoredProcedure [dbo].[UP_GetPageList]    Script Date: 08/24/2013 11:22:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[UP_GetPageList]
(
@tbName   varchar(255),       -- 表名
@fields varchar(1000) = '*', -- 需要返回的列 
@order varchar(255)='1',      -- 排序的字段名
@pageSize   int = 10,          -- 页尺寸
@pageIndex int = 1,          -- 页码
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@count int = 0 output,   -- 返回记录总数, 非 0 值则返回
@orderType bit = 0 -- 设置排序类型, 非 0 值则降序
)
AS
declare @strSQL   nvarchar(4000)       -- 主语句
declare @strTmp   varchar(110)       -- 临时变量
declare @strOrder varchar(400)       -- 排序类型
declare @getvalue varchar(400)
declare @rowOrder varchar(100)
    set @strSQL = 'select @count= count(1) from ' + @tbName + ' where 1=1 '+@strWhere


    print @strSQL
   exec sp_executesql @strSQL,N'@count int out',@Count out
    print @count
    
    
declare @sql nvarchar(2000) 
declare @cou int 


begin
if @orderType = 0
begin
    set @strTmp = '>(select max'
set @strOrder = ' order by ' + @order +' desc'
set @rowOrder=' row  '
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by ' + @order +' asc'
    set @rowOrder=' row desc '
end
if @PageIndex = 1
begin
print @fields
    set @strSQL = 'select top ' + str(@PageSize) +' '+@fields+ '  from ' + @tbName + ' where 1=1 ' + @strWhere + ' ' + @strOrder


--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'with tempTb as(select top ' + str(@Count) +' '+@fields+ ' from '
    + @tbName + ' where 1=1 ' +@strWhere+ @strOrder +' )'
   set @strSql+='select top '+ str(@PageSize) +' * from tempTb where row '+@strTmp+'(row) from (select top '+ str(@PageSize*(@pageIndex-1)) +' row from tempTb) as row) order by   '+@rowOrder


end 
end
print @strSQL
exec (@strSQL)