sql2000和sql2005分页存储过程
MSSQL2000

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)
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)
MSSQL2005






















































留着自己看