SQL2005下通用分页存储过程，支持多字段排序，多表连接，多表嵌套

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[sp_GetRecordFromPage] @TableName varchar(350), --表名 @Fields varchar(5000) = '*', --字段名(全部字段为*) @OrderField varchar(5000), --排序字段(必须!支持多字段) @sqlWhere varchar(5000) = Null,--条件语句(不用加where) @pageSize int, --每页多少条记录 @pageIndex int = 1, --指定当前为第几页 @distinct VARCHAR(50)=NULL, --去除重复值，注意只能是一个字段 @top INT=NULL --查询TOP,不传为全部 AS BEGIN Declare @sql nvarchar(4000); Declare @totalRecord int; DECLARE @totalPage INT; --计算总记录数 IF (@distinct IS NULL OR @distinct='') BEGIN IF (@SqlWhere='' OR @sqlWhere IS NULL) SET @sql = 'select @totalRecord = count(1) from ' + @TableName ELSE SET @sql = 'select @totalRecord = count(1) from ' + @TableName + ' where ' + @sqlWhere END ELSE BEGIN IF (@SqlWhere='' OR @sqlWhere IS NULL) SET @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName ELSE SET @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName + ' where ' + @sqlWhere END EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 IF(@top>0) BEGIN --指定TOP 记录 SET @Fields= 'top ' + CAST(@top AS VARCHAR(20)) + ' ' + @Fields; --如果总记录数超过TOP数,设总记录数为TOP数 IF(@totalRecord>@top) SET @totalRecord=@top END --计算总页数 SELECT @totalPage=CEILING((@totalRecord+0.0)/@PageSize) SELECT @totalRecord AS 'fldtotalRecord',@totalPage AS 'fldTotalPage' IF (@distinct IS NULL OR @distinct='') BEGIN IF (@SqlWhere='' or @sqlWhere IS NULL) SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName ELSE SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere END ELSE BEGIN IF (@SqlWhere='' or @sqlWhere IS NULL) SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName ELSE SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere SET @sql=@sql + ' GROUP BY ' + @distinct; END --处理页数超出范围情况 IF @PageIndex<=0 SET @pageIndex = 1 --处理开始点和结束点 DECLARE @StartRecord INT DECLARE @EndRecord int SET @StartRecord = (@pageIndex-1)*@PageSize + 1 SET @EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句 SET @sql = @sql + ') as tempTable where rowId >=' + CONVERT(VARCHAR(50),@StartRecord) + ' and rowid<= ' + CONVERT(VARCHAR(50),@EndRecord) Exec(@sql) end

来源：http://www.qx-net.cn/post/2008/09/08/SQL2005e4b88be9809ae794a8e58886e9a1b5e5ad98e582a8e8bf87e7a88befbc8ce694afe68c81e5a49ae5ad97e6aeb5e68e92e5ba8fefbc8ce5a49ae8a1a8e8bf9ee68ea5efbc8ce5a49ae8a1a8e5b58ce5a597.aspx