sql server 分页存储过程

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE comm_PagingInfo
 @tblName   varchar(255),       -- 表名

@strGetFields varchar(1000) = '*',  -- 需要返回的列

@fldName varchar(255)='',      -- 排序的字段名

@PageSize   int = 10,          -- 页尺寸

@PageIndex  int = 1,           -- 页码

@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回

@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序

@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)       -- 主语句

declare @strTmp   varchar(110)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型

--检查对象是否有效
IF OBJECT_ID(@tblName) IS NULL
BEGIN
 RAISERROR(N'对象"%s"不存在',1,16,@tblName)
 RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tblName),N'IsTable')=0
 AND OBJECTPROPERTY(OBJECT_ID(@tblName),N'IsView')=0
 AND OBJECTPROPERTY(OBJECT_ID(@tblName),N'IsTableFunction')=0
BEGIN
 RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tblName)
 RETURN
END
 

if @doCount != 0
 begin
     if @strWhere !=''
      set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
      else
     set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
 else if @PageSize= 0 --表示不分页
    begin
         if @strWhere !=''
      set @strSQL = "select  “+@strGetFields  +  from [" + @tblName + "] where "+@strWhere
      else
     set @strSQL = "select count(*) as Total from [" + @tblName + "]"
        if @OrderType != 0
 begin     
  set @strOrder = "  order by [" + @fldName +"] desc" 
 end
 --如果@OrderType不是0，就执行降序，这句很重要！
 else
 begin                                                                                                                                                                          
      set @strOrder = "  order by [" + @fldName +"] asc"
 end
           set @strSQL = @strSQL +  @strOrder
 
 end
      
else
begin
 if @OrderType != 0
 begin
      set @strTmp = "<(select min"
  set @strOrder = " order by [" + @fldName +"] desc" 
 end
 --如果@OrderType不是0，就执行降序，这句很重要！
 else
 begin                                                                                                                                                                          
      set @strTmp = ">(select max"
     set @strOrder = " order by [" + @fldName +"] asc"
 end
 

 if @PageIndex = 1
 begin
      if @strWhere != ''  
       set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from [" + @tblName + "] where " + @strWhere + " " + @strOrder
   else
   set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["+ @tblName + "] "+ @strOrder
 end
 --如果是第一页就执行以上代码，这样会加快执行速度
 else
 --以下代码赋予了@strSQL以真正执行的SQL代码
 begin
  set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["
       + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize)
   + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

  if @strWhere != ''
  begin
       set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["
            + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
            + @fldName + "]) from (select top " + str((@PageIndex -1)*@PageSize) + " ["
            + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
            + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
  end
 end
end  

exec (@strSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO