SQL2000和SQL2005分页存储过程
MSSql 2000
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Proc_Page2000] ( @TabName nvarchar(100)='',--表名、视图名、查询语句 @PageSize int=10, --每页的大小(行数) @PageIndex int=1, --要显示的页 从0开始 @FieldShow nvarchar (200)='*', --要显示的字段列表 @FieldKey nvarchar (100)='id', --主键 @WhereStr nvarchar (2000)='1=1', @FieldOrder int=1, --排序 1desc0asc @RecordCount int output ) as declare @sql nvarchar(2000) if (@WhereStr = '') begin set @WhereStr = '1=1' end declare @tsql nvarchar(200) set @tsql=N'select @RecordCount = count(1) from ' + @TabName + ' where ' + @WhereStr exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output if(@PageIndex>1) begin set @sql='select top '+ltrim(str(@PageSize))+' '+@FieldShow+' from '+@TabName+ ' where '+ @WhereStr if(@FieldOrder=1) begin set @sql=@sql+' and '+@FieldKey+'<(select min('+@FieldKey+') from (select top '+ltrim(str(@PageSize*(@PageIndex-1)))+' '+@FieldKey+' from '+@TabName+' where '+@WhereStr+' order by '+@FieldKey+' desc) as t) order by '+@FieldKey+' desc' end else begin set @sql=@sql+' and '+@FieldKey+'>(select max('+@FieldKey+') from (select top '+ltrim(str(@PageSize*(@PageIndex-1)))+' '+@FieldKey+' from '+@TabName+' where '+@WhereStr+' order by '+@FieldKey+' asc) as t) order by '+@FieldKey+' desc' end end else begin set @sql='select top '+ltrim(str(@PageSize))+' '+@FieldShow+' from '+@TabName+ ' where '+ @WhereStr if(@FieldOrder=1) begin set @sql=@sql+' order by '+@FieldKey+' desc' end else begin set @sql=@sql+' order by '+@FieldKey+' asc' end end execute(@sql)
MSSql 2005
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[PROCE_SQL2005PAGECHANGE] ( @TableName varchar(50), --表名 @ReFieldsStr varchar(200) = '*', --字段名(全部字段为*) @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N'', --条件语句(不用加where) @PageSize int, --每页多少条记录 @PageIndex int = 1 , --指定当前为第几页 @TotalRecord int output --返回总记录数 ) AS BEGIN --处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar(500); Declare @SqlString nvarchar(2000); set @StartRecord = (@PageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句 -- IF (@WhereString! = '' or @WhereString!=null) BEGIN SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString; SET @SqlString =@SqlString+ ' where '+ @WhereString; END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord)); Exec(@SqlString) END