62,074
社区成员
发帖
与我相关
我的任务
分享
create proc testProc
(
@placeNumber varchar(32), --场所编号
@placeName nvarchar(50), --场所名称
@PageIndex int = 0, --页码
@PageSize int = 10, --每页记录数
@RecordCount int output --记录总数
)
as
declare @sqlStr nvarchar(2000),@sqlStrEnd nvarchar(10)
begin
select * from EnvirInfo order by placeNumber
select @RecordCount = count(distinct placeNumber)
from EnvirInfo
set @sqlStr = 'select row_number() over(order by placeNumber ) as rownum, * from (select distinct * from EnvirInfo where 1=1 '
if @placeNumber = ''
begin
if @placeName <>''
begin
set @sqlStr = @sqlStr + 'and placeName = ' + @placeName;
end
end
else
begin
if @placeName <>''
begin
set @sqlStr = @sqlStr + 'and placeName = ' + @placeName + ' and placeNumber = ' + @placeNumber;
end
else
begin
set @sqlStr = @sqlStr + 'and placeNumber = ' + @placeNumber;
end
end
set @sqlStrEnd = ') '
set @sqlStr = @sqlStr + @sqlStrEnd
--重置当前页号
if (@RecordCount / @PageSize < @PageIndex+1)
set @PageIndex = @RecordCount / @PageSize
;
set @sqlStr = 'with temp as ( ' + @sqlStr;
set @sqlStr = @sqlStr + ') '
exec @sqlStr
--分页处理
select * from temp where a.rownum between (@PageIndex * @PageSize + 1) and (@PageIndex + 1) * @PageSize
end
@sqlStr = @sqlStr + ' select * from temp where a.rownum between ('+cast(@PageIndex as varchar(50))+' * '+cast(@PageSize as varchar(50))+' + 1) and ('+cast(@PageIndex as varchar(50))+' + 1) * '+cast(@PageSize as varchar(50))+''
@sqlStr = @sqlStr + ' select * from temp where a.rownum between ('+cast(@PageIndex as varchar(50))+' * '+cast(@PageSize as varchar(50))+' + 1) and ('+cast(@PageIndex as varchar(50))+' + 1) * '+cast(@PageSize as varchar(50))+''
public
create proc testProc
(
@placeNumber varchar(32), --场所编号
@placeName nvarchar(50), --场所名称
@PageIndex int = 0, --页码
@PageSize int = 10, --每页记录数
@RecordCount int output --记录总数
)
as
declare @sqlStr nvarchar(2000),@sqlStrEnd nvarchar(10)
begin
select * from EnvirInfo order by placeNumber
select @RecordCount = count(distinct placeNumber)
from EnvirInfo
set @sqlStr = 'select row_number() over(order by placeNumber ) as rownum, * from (select distinct * from EnvirInfo where 1=1 '
if @placeNumber = ''
begin
if @placeName <>''
begin
set @sqlStr = @sqlStr + 'and placeName = ' + @placeName;
end
end
else
begin
if @placeName <>''
begin
set @sqlStr = @sqlStr + 'and placeName = ' + @placeName + ' and placeNumber = ' + @placeNumber;
end
else
begin
set @sqlStr = @sqlStr + 'and placeNumber = ' + @placeNumber;
end
end
set @sqlStrEnd = ') '
set @sqlStr = @sqlStr + @sqlStrEnd
--重置当前页号
if (@RecordCount / @PageSize < @PageIndex+1)
set @PageIndex = @RecordCount / @PageSize
;
set @sqlStr = 'with temp as ( ' + @sqlStr;
set @sqlStr = @sqlStr + ') '
--exec @sqlStr
--分页处理
@sqlStr = @sqlStr + ' select * from temp where a.rownum between ('+cast(@PageIndex as varchar(50))+' * '+cast(@PageSize as varchar(50))+' + 1) and ('+cast(@PageIndex as varchar(50))+' + 1) * '+cast(@PageSize as varchar(50))+''
exec @sqlStr
--select * from temp where a.rownum between (@PageIndex * @PageSize + 1) and (@PageIndex + 1) * @PageSize
end
@sqlStr = @sqlStr + ' select * from temp where a.rownum between ('+cast(@PageIndex as varchar(50))+' * '+cast(@PageSize as varchar(50))+' + 1) and ('+cast(@PageIndex as varchar(50))+' + 1) * '+cast(@PageSize as varchar(50))+''
@sqlStr = @sqlStr + ' select * from temp where a.rownum between ('+cast(@PageIndex as varchar(50))+' * '+cast(@PageSize as varchar(50))+' + 1) and ('+cast(@PageIndex as varchar(50))'+ + 1) * '+cast(@PageSize as varchar(50))+''
@sqlStr = @sqlStr +' select * from temp where a.rownum between ('+cast(@PageIndex as varchar(50))+' * '+cast(@PageSize as varchar(50))+' + 1) and ('+cast(@PageIndex as varchar(50))'+ + 1) * '+cast(@PageSize as varchar(50))+''
exec @sqlStr