SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
use master
go
alter proc WebPager
@TableName nvarchar(50), --表名称
@ReturnFields nvarchar(2000),--需要返回的列
@PageSize int = 20,--每页显示的条数
@PageIndex int = 1,--记录开始数
@Where nvarchar(2000) = '',--查询条件,当前为空
@Orderfld nvarchar(2000), -- 排序字段名 最好为唯一主键
--@PageCount int output, --总页数
@ItemCount bigint output,--总记录数
@DoCount bit = 0 --是否统计总数,为0不统计,为1统计 as
begin
set nocount on;
declare @sql nvarchar(4000);
declare @totalRecord int;
--计算总记录数
if (@Where ='''' or @Where='' or @Where is NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @Where
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@ItemCount OUTPUT--计算总记录数
declare @SqlQuery nvarchar(max) if(@PageIndex=1 or @PageIndex=0 or @PageIndex <0)
begin
--1
if(@Where is null)
--语法:select row_number() over(order by field1) as row_number,* from t_table
set @SqlQuery='select top '+convert(nvarchar,@PageSize)+' row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields
+' from '+@TableName;
else
set @SqlQuery='select top '+convert(nvarchar,@PageSize)+' row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields
+' from '+@TableName+' where'+@Where;
endelse
begin
--2
if(@Where is null)
begin
--使用公用表表达式(CTE)简化嵌套SQL,CTE的语法: with ‘名’ as(sql)
set @SqlQuery='with ct as(select row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields+' from '+@TableName+')
select * from ct where RowNumber between'+ convert(nvarchar,@PageIndex)+'and'+convert(nvarchar,@PageIndex+@PageSize)
end
else
begin
set @SqlQuery='with ct as(select row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields+' from '+@TableName+' where'+@Where+')
select * from ct where RowNumber between'+ convert(nvarchar,@PageIndex)+'and'+convert(nvarchar,@PageIndex+@PageSize)
end
end
print @SqlQuery
exec (@SqlQuery)
end
go
GO
SET QUOTED_IDENTIFIER ON
GO
use master
go
alter proc WebPager
@TableName nvarchar(50), --表名称
@ReturnFields nvarchar(2000),--需要返回的列
@PageSize int = 20,--每页显示的条数
@PageIndex int = 1,--记录开始数
@Where nvarchar(2000) = '',--查询条件,当前为空
@Orderfld nvarchar(2000), -- 排序字段名 最好为唯一主键
--@PageCount int output, --总页数
@ItemCount bigint output,--总记录数
@DoCount bit = 0 --是否统计总数,为0不统计,为1统计 as
begin
set nocount on;
declare @sql nvarchar(4000);
declare @totalRecord int;
--计算总记录数
if (@Where ='''' or @Where='' or @Where is NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @Where
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@ItemCount OUTPUT--计算总记录数
declare @SqlQuery nvarchar(max) if(@PageIndex=1 or @PageIndex=0 or @PageIndex <0)
begin
--1
if(@Where is null)
--语法:select row_number() over(order by field1) as row_number,* from t_table
set @SqlQuery='select top '+convert(nvarchar,@PageSize)+' row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields
+' from '+@TableName;
else
set @SqlQuery='select top '+convert(nvarchar,@PageSize)+' row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields
+' from '+@TableName+' where'+@Where;
endelse
begin
--2
if(@Where is null)
begin
--使用公用表表达式(CTE)简化嵌套SQL,CTE的语法: with ‘名’ as(sql)
set @SqlQuery='with ct as(select row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields+' from '+@TableName+')
select * from ct where RowNumber between'+ convert(nvarchar,@PageIndex)+'and'+convert(nvarchar,@PageIndex+@PageSize)
end
else
begin
set @SqlQuery='with ct as(select row_number() over(order by '+@Orderfld+') as RowNumber,'+@ReturnFields+' from '+@TableName+' where'+@Where+')
select * from ct where RowNumber between'+ convert(nvarchar,@PageIndex)+'and'+convert(nvarchar,@PageIndex+@PageSize)
end
end
print @SqlQuery
exec (@SqlQuery)
end
go
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货