sql server 高效分页存储过程实例
本文导语: 高效分页存储过程一，代码如下： 代码示例: USE [XHXFZ] GO /****** 对象: StoredProcedure [dbo].[SP_Page2005] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[SP_Page2005] @TableName varchar(5000), --表名 @Fields va...
高效分页存储过程一，代码如下：
USE [XHXFZ]
GO
/****** 对象: StoredProcedure [dbo].[SP_Page2005] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[SP_Page2005]
@TableName varchar(5000), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段，逗号隔开)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@IsStatCounts int = 0, --是否统计记录数(0不统计，1统计)
@Counts int = 0 output, --查询到的记录数
@TotalPage int = 0 output --返回总页数
as
begin
SET NOCOUNT ON
--Begin Tran --开始事务
Declare @sql nvarchar(4000);
Declare @totalRecord int ;
set @totalRecord=0;
--计算总记录数
if( @IsStatCounts =1)
begin
if (@SqlWhere='' or @sqlWhere is NULL)
begin
set @sql = 'select @totalRecord = count(*) from ' + @TableName
end
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
set @Counts=@totalRecord
--计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
end
if (@SqlWhere='' or @sqlWhere is NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex@TotalPage
Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as selectTable where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
-- print @Sql
Exec sp_executesql @Sql
---------------------------------------------------
-- If @@Error 0
-- Begin
-- RollBack Tran
-- Return -1
-- End
-- Else
-- Begin
-- Commit Tran
-- Return @totalRecord ---返回记录总数
-- End
end
2，简单高效的分页解决方案
项目需求:
数据库有5000条记录，每页显示20条，要求实现每翻一页就读取当前页的20条数据;
解决方案:
根据传入的页码和每页显示的记录数查询出相应的数据
1>第一页:select Top 10 * from TableName
2>以后各页:select row_number() over(order by code) rownum,* into #table from [tableName]
select * from #table where rownum in (@CurrentPageIndex*@PageSize+1,@CurrentPageIndex*@PageSize+10)
//注：（@CurrentPageIndex为传入的当前页的页码,@PageSize为每页显示的记录的条数)
代码如下：
alter PROCEDURE [dbo].[sp_PageView]
@strSql nvarchar(1000),
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@PageCount int OUTPUT, --总页数
@ListCount int OUTPUT --数据总条数
AS
BEGIN
IF @PageCount IS NULL OR @PageCount=0
BEGIN
declare @sqlListCount nvarchar(2000),@sqlstr nvarchar(2000),@mod int,@except int
set @sqlListCount=N'select @a=count(1) from ('+@strSql+')t'
exec sp_executesql @sqlListCount,N'@a int output',@ListCount output
set @mod = @ListCount % @PageSize
set @except= @ListCount / @PageSize
if @mod=0
begin
set @PageCount =@except
end
else
begin
set @PageCount =@except+1
end
END
set @sqlstr=N'select * from ('
+N'select row_number() over (order by '+@FieldKey+') rownum,'+substring(ltrim(lower(@strSql)),7,3000)
+N')z where rownum between '+cast(((@PageCurrent-1)*@PageSize+1) as nvarchar(20))
+N' and '+cast(@PageCurrent*@PageSize as nvarchar(20))
exec (@sqlstr)
--select @ListCount,@PageCount
END
您可能感兴趣的文章:
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。