两个分页存储过程的用法
作者: 青羽 , 出处:博客园, 责任编辑: 李书琴,
2008-01-21 12:00
本文介绍了两个分页存储过程[Basic_Pagination2005]和[Basic_Pagination2000]的用法……
2。Basic_Pagination2005
| 以下是引用片段： ALTER PROCEDURE [dbo].[Basic_Pagination2005] @tblName nvarchar(200), --表名 @fidlelist nvarchar(1000), --要查询字段 @fldName nvarchar(100), --排序字段 @PageSize int, --页尺寸 @PageIndex int, --页码 @IsReCount bit , -- 返回记录总数, 非 0 值则返回 @OrderType bit, -- 设置排序类型, 非 0 值则降序 @strWhere nvarchar(1000) --查询条件 AS declare @sqlstr nvarchar(4000),@tmpwhere nvarchar(4000),@tmporder nvarchar(100) BEGIN if @OrderType != 0 begin set @tmporder = @fldName +' desc ' end else begin set @tmporder = @fldName +' asc ' end set @tmpwhere=''; if(@strWhere!='') begin set @tmpwhere=' where '+@strWhere; end set @sqlstr=N'select * from(select '+@fidlelist+', ROW_NUMBER() OVER(order by '+@tmporder+') as row from '+@tblName+@tmpwhere+') tmp where row between '+cast(((@PageIndex-1)*@PageSize+1) as nvarchar)+' and '+cast(@PageIndex*@PageSize as nvarchar); exec sp_executesql @sqlstr if @IsReCount != 0 begin set @sqlstr=N'select count(*) as Total from '+ @tblName+@tmpwhere exec sp_executesql @sqlstr end END |
- 本文关键词：
