数据库分页

自己写的 还算通用 呵呵 备用

采用的是TOP方法；经测试速度还算不错，注释部分有MAX方法，速度 第一页最快，

存储过程

USE [csrl]
GO
/****** Object:  StoredProcedure [dbo].[PageIndex]    Script Date: 12/07/2011 10:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PageIndex]
(
@Tbname varchar(100),--表名字
 @Strfield varchar(100),--查询字段
 @idfield varchar(50),--主键ID
 @Toppage int=10,--每页行数
 @PageNow int=0,--查询页数
 @strWhere varchar(100) ='1=1',--查询条件
 @Pagecount int=0 output,--总页数
 @order varchar(200)=''--排序条件
) 
AS 
DECLARE @sql nvarchar(1000),
@sqloder varchar(200)
BEGIN
if(@Pagecount=0)--总页数
        begin
declare @page int
set @sql= 'select @Page=COUNT('+@idfield+') from '+@Tbname+ ' where '+@strWhere
print @sql
exec sp_executesql @sql,N'@Page int output',@Page output--查询总行数    
            SELECT @pageCount=CEILING((@page+0.0)/@Toppage) --计算页数
            print @pageCount
end
if(@order!='')--排序条件
        begin
set @sqloder='ORDER BY '+@idfield
end
else
begin
set @sqloder=''
end
if(@strWhere!='')--查询条件
    begin
set @strWhere='where '+@strWhere
end
begin--执行SQL语句
    --/*
        set @sql='SELECT TOP '
+cast(@Toppage as varchar(200))+
' '+@Strfield+' FROM (SELECT ROW_NUMBER() OVER (ORDER BY '
+@idfield+') AS RowNumber, '+@Strfield+
' FROM Module) A WHERE RowNumber > '
+cast(@Toppage as varchar(200))+' * ('
+cast(@PageNow as varchar(200))+'-1) '+@sqloder
--*/
        --print @sql
        /*set @sql ='SELECT TOP '+cast(@Toppage as varchar(200))+' '+@Strfield+ '
                FROM '+@Tbname+'
                WHERE '+@idfield+' > 
                        ( 
                            SELECT ISNULL(MAX('+@idfield+'),0) 
                            FROM 
                        ( 
                        SELECT TOP ('+cast(@Toppage as varchar(200))+'*('+cast(@PageNow as varchar(200))+'-1)) 
                            '+@idfield+' FROM '+@Tbname+' '+@sqloder+' 
                            ) A 
                        ) '+@sqloder*/
print (@sql)
exec (@sql)

end     
END

 

转载于:https://www.cnblogs.com/zzqnet/archive/2011/12/07/2278843.html