mssql 2005 分页

USE [WPTGData]
GO
/****** 对象:  StoredProcedure [dbo].[Proc_OP_Pager]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Proc_OP_Pager]
/***************************************************************
参数说明:
1.Tables :表名称,视图，不带dbo.
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc (Desc Asc前必须加空格)
4.Fields: 字段名
5.Filter :过滤语句，不带Where
6.Group :Group语句,不带Group By
***************************************************************/
(
  @Tables varchar(3000),
  @PrimaryKey varchar(1000) = NULL,
  @Sort varchar(2000) = NULL,
  @CurrentPage int = 1,
  @PageSize int = 20,
  @Fields varchar(3000) = '*',
  @Filter varchar(2000) = NULL,
  @Group varchar(1000) = NULL,
  @CountNum int=0 output
)AS
BEGIN
   SET NOCOUNT ON
   /*默认排序*/
   SET @Sort = RTRIM(LTRIM(@Sort))
   IF @Sort IS NULL OR @Sort = ''
      SET @Sort = @PrimaryKey
      DECLARE @SortTable varchar(100)
      DECLARE @SortName varchar(100)
      DECLARE @SQL1 nvarchar(4000)
      /*设定排序语句.*/
      DECLARE @strPageSize varchar(50)
      DECLARE @strStartRow varchar(50),@strEndRow varchar(50)
      DECLARE @strFilter varchar(2000)
      DECLARE @strGroup varchar(1000)
      IF @CurrentPage < 1
       SET @CurrentPage = 1
       /*设置分页参数.*/
       SET @strPageSize = CAST(@PageSize AS varchar(50))
       SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
       SET @strEndRow = CAST((@CurrentPage*@PageSize) AS varchar(50))
   /*筛选以及分组语句.*/
   IF @Filter IS NOT NULL AND @Filter != ''
      BEGIN
      SET @strFilter = ' Where ' + @Filter + ' '
      END
   ELSE
      BEGIN
      SET @strFilter = ''
      END
   IF @Group IS NOT NULL AND @Group != ''
      BEGIN
      SET @strGroup = ' GROUP BY ' + @Group + ' '
      END
   ELSE
      BEGIN
      SET @strGroup = ''
      END
   /*执行查询语句*/
   SET @SQL1 ='select * from (Select ' + @Fields + ',ROW_NUMBER() OVER(Order By '+@Sort+') as row FROM ' + @Tables + @strFilter + @strGroup + ') a where row between '+@strStartRow+' and '+@strEndRow
   --PRINT (@SQL1)
   EXEC (@SQL1)
   select @SQL1
   if (@strGroup = '') or (@strGroup is null)
      begin
        set @SQL1 ='select @CountNum=count(*) from '+ @Tables + @strFilter
      end
   else
      begin
        set @SQL1 ='select @CountNum=count(*) from (select 1 _Counter from '+ @Tables + @strFilter + @strGroup+') _TMP'
      end
   select @SQL1
   exec sp_executesql @SQL1,N'@CountNum int output',@CountNum output 
END

转载于:https://www.cnblogs.com/dev-xp/archive/2010/10/29/1864666.html