存储过程 为 SQL Server 2000版本,请打开SQL server 2000 的查询分析器执行下面的SQL 语句。
程序用到的存储过程(仅支持主键排序)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_QuickSortPaging]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[sys_QuickSortPaging]GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOCREATE PROCEDURE sys_QuickSortPaging( @Table nvarchar(4000), --表名(必须) @PrimaryKeyField nvarchar(50), --表的主键字段 @Field nvarchar (4000)='*', --需要返回字段名(必须) @Where nvarchar(1000)=NULL, --Where 条件(可选) @GroupBy nvarchar(1000) = NULL, --分组 @OrderBy nvarchar(1000)=NULL, --排序用到的字段() @PageNumber int = 1, --要返回的页(第X页) (默认为第一页) @PageSize int = 10, --每页大小(默认为5) @RecordCount int output --返回记录总数)AS SET NOCOUNT ON DECLARE @SortTable nvarchar(100) DECLARE @SortName nvarchar(100) DECLARE @strSortColumn nvarchar(200) DECLARE @Operator nvarchar(50) DECLARE @Type varchar(100) DECLARE @Prec int IF @OrderBy IS NULL OR @OrderBy = '' SET @OrderBy = @PrimaryKeyField /**//* 获取用于定位的字段*/ IF CHARINDEX('DESC',@OrderBy)>0 BEGIN SET @strSortColumn = REPLACE(@OrderBy, 'DESC', '') SET @Operator = '<=' END ELSE BEGIN IF CHARINDEX('ASC', @OrderBy) = 0 SET @strSortColumn = REPLACE(@OrderBy, 'ASC', '') SET @Operator = '>=' END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN SET @SortTable = @Table SET @SortName = @strSortColumn END SELECT @Type=t.name, @Prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @Type) > 0 SET @Type = @Type + '(' + CAST(@Prec AS nvarchar) + ')' DECLARE @strStartRow nvarchar(50) DECLARE @strPageSize nvarchar(50) DECLARE @strWhere nvarchar(1000) DECLARE @strWhereAnd nvarchar(1000) DECLARE @strGroupBy nvarchar(1000) IF @PageNumber < 1 SET @PageNumber = 1 SET @strPageSize = CONVERT (nvarchar(50), @PageSize) SET @strStartRow = CONVERT ( nvarchar(50), (@PageNumber - 1)*@PageSize + 1) IF @Where IS NOT NULL AND @Where !='' BEGIN SET @strWhere = ' WHERE '+ @Where SET @strWhereAnd= ' AND ' + @Where END ELSE BEGIN SET @strWhere = '' SET @strWhereAnd='' END IF @GroupBy IS NOT NULL AND @GroupBy != '' BEGIN SET @strGroupBy = ' GROUP BY ' + @GroupBy END ELSE BEGIN SET @strGroupBy = '' END DECLARE @strSQL nvarchar(4000) SET @strSql= ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy EXEC sp_executesql @strSql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT--计算总页数 EXEC ( ' DECLARE @Sort ' + @Type + ' SET ROWCOUNT ' + @strStartRow + ' SELECT @Sort = ' + @strSortColumn + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + ' SET ROWCOUNT ' + @strPageSize + ' SELECT '+@Field+' FROM ' + @Table + ' WHERE ' + @strSortColumn + @Operator + ' @Sort ' + @strWhereAnd + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy )GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO支持任意字段排序的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_SortDataPager]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[sys_SortDataPager]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE sys_SortDataPager ( @Table nvarchar(4000), --表名(必须) @PrimaryKeyField nvarchar(50), --表的主键字段 @Field nvarchar (4000)='*', --需要返回字段名(必须) @Where nvarchar(1000)=NULL, --Where 条件(可选) @GroupBy nvarchar(1000) = NULL, --分组 @OrderBy nvarchar(1000)=NULL, --排序用到的字段() @PageNumber int = 1, --要返回的页(第X页) (默认为第一页) @PageSize int = 10, --每页大小(默认为5) @RecordCount int out --返回记录总数)AS /**//*Find the @PrimaryKeyField type*/ DECLARE @PKTable varchar(1000) DECLARE @PKName varchar(1000) DECLARE @type varchar(1000) DECLARE @prec int IF CHARINDEX('.', @PrimaryKeyField) > 0 BEGIN SET @PKTable = SUBSTRING(@PrimaryKeyField, 0, CHARINDEX('.',@PrimaryKeyField)) SET @PKName = SUBSTRING(@PrimaryKeyField, CHARINDEX('.',@PrimaryKeyField) + 1, LEN(@PrimaryKeyField)) END ELSE BEGIN SET @PKTable = @Table SET @PKName = @PrimaryKeyField END SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @PKTable AND c.name = @PKName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize varchar(50) DECLARE @strStartRow varchar(50) DECLARE @strWhere varchar(1000) DECLARE @strGroupBy varchar(1000) /**//*Default Sorting*/ IF @OrderBy IS NULL OR @OrderBy = '' SET @OrderBy = @PrimaryKeyField /**//*Default Page Number*/ IF @PageNumber < 1 SET @PageNumber = 1 /**//*Set paging variables.*/ SET @strPageSize = CAST(@PageSize AS varchar(50)) SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50)) /**//*Set filter & group variables.*/ IF @Where IS NOT NULL AND @Where != '' SET @strWhere = ' WHERE ' + @Where + ' ' ELSE SET @strWhere = '' IF @GroupBy IS NOT NULL AND @GroupBy != '' SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' ' ELSE SET @strGroupBy = '' /**//*Execute dynamic query*/ DECLARE @strSQL nvarchar(4000) SET @strSql= ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy EXEC sp_executesql @strSql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT--计算总页数 EXEC( 'DECLARE @PageSize int SET @PageSize = ' + @strPageSize + ' DECLARE @PrimaryKeyField ' + @type + ' DECLARE @tblPK TABLE ( PK ' + @type + ' NOT NULL PRIMARY KEY ) DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR SELECT ' + @PrimaryKeyField + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + ' OPEN PagingCursor FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PrimaryKeyField SET NOCOUNT ON WHILE @PageSize > 0 AND @@FETCH_STATUS = 0 BEGIN INSERT @tblPK (PK) VALUES (@PrimaryKeyField) FETCH NEXT FROM PagingCursor INTO @PrimaryKeyField SET @PageSize = @PageSize - 1 END CLOSE PagingCursor DEALLOCATE PagingCursor SELECT ' + @Field + ' FROM ' + @Table + ' JOIN @tblPK tblPK ON ' + @PrimaryKeyField + ' = tblPK.PK ' + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy )GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO创建一个用于测试的表
CREATE TABLE [dbo].[Employees] ( [EmployeesID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [LastName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [FirstName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [BirthDate] [datetime] NULL , [Address] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL , [City] [nvarchar] (18) COLLATE Chinese_PRC_CI_AS NULL , [HomePhone] [nvarchar] (24) COLLATE Chinese_PRC_CI_AS NULL , [Extension] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO生成 1000000 条测试数据
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[InsertTableData]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS OFF GOCREATE PROCEDURE InsertTableData ASDECLARE @cnt bigintSET @cnt = 0WHILE @cnt < 1000000 BEGIN INSERT INTO Employees(LastName,FirstName,BirthDate,Address,City,HomePhone,Extension) Values ( CAST('LastName ' +CONVERT(nvarchar(10), @cnt) as nvarchar(30)), CAST('FirstName '+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)), GETDATE(), CAST('Address IS No.'+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)), CAST('City '+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)), CAST('021-0000'+ LEFT(CONVERT(nvarchar(10), @cnt),4) as nvarchar(30)), CAST('00' + LEFT(CONVERT(nvarchar(10), @cnt) ,1) as nvarchar(30)) ) SET @cnt = @cnt + 1 ENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO