sqlserver 分页

USE [HR_CheckIn]
GO

/****** Object:  StoredProcedure [dbo].[DCJET_PORTAL_PAGER_WITH_NotIn]    Script Date: 2015-12-23 9:59:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER   PROCEDURE [dbo].[DCJET_PORTAL_PAGER_WITH_NotIn]
            @tblName      VARCHAR(5000), -- 表名
           @strGetFields VARCHAR(5000) = '*', -- 需要返回的列
           @fldName      VARCHAR(255) = '', -- 排序的方式,如：List_No asc ,List_G_No desc
           @PageSize     INT = 10, -- 页尺寸
           @PageIndex    INT = 1, -- 页码
           @strKey       VARCHAR(100) = 'id',--主键    
           @strWhere     VARCHAR(1500) = '' -- 查询条件 (注意: 不要加 where)                                
AS
  DECLARE
    @strSQL VARCHAR(8000) -- 主语句
  DECLARE
    @strSqlCount VARCHAR(8000) -- 主语句
  DECLARE
    @strSqlNoPage VARCHAR(8000) -- 主语句
  DECLARE
    @strOrder VARCHAR(400) -- 排序类型
  
  SET @strWhere = LTRIM(RTRIM(@strWhere))
  IF @strWhere != ''
    SET @strSqlCount = 'select count(*) as Total from ' + @tblName + ' where ' + @strWhere
   ELSE
    SET @strSqlCount = 'select count(*) as Total from ' + @tblName + ''

  SET @strOrder = ' order by ' + @fldName
  IF @strWhere != ''
    SET @strSqlNoPage = 'select  ' + @strGetFields + '  from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
   ELSE
    SET @strSqlNoPage = 'select   ' + @strGetFields + '  from ' + @tblName + ' ' + @strOrder
  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
    BEGIN
    --以下代码赋予了@strSQL以真正执行的SQL代码
      IF @strWhere != ''
        SET @strSQL = 'select top ' + STR(@PageSize) + ' ' + @strGetFields + '  from ' + @tblName + ' where ' + @strKey + '' + ' not in (select top ' + STR((@PageIndex - 1) * @PageSize) + ' ' + @strKey + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') and ' + @strWhere + ' ' + @strOrder
       ELSE
        SET @strSQL = 'select top ' + STR(@PageSize) + ' ' + @strGetFields + '  from ' + @tblName + ' where ' + @strKey + ' not in (select top ' + STR((@PageIndex - 1) * @PageSize) + ' ' + @strKey + ' from ' + @tblName + '' + @strOrder + ')' + @strOrder
    END
    
  PRINT @strSqlCount
  EXEC( @strSqlCount)
  
  PRINT @strSQL
  EXEC( @strSQL)
  
  PRINT @strSqlNoPage
  SELECT @strSqlNoPage





GO