sql 分页rownumber方式
时间：2021-07-01 10:21:17
帮助过：2人阅读
procedure [dbo].
[proc_getpaging]
(
@TableName nvarchar(
500),
--表名（可以为多表）
@ReFieldsStr nvarchar(
200)
= ‘*‘,
--字段名(全部字段为*)
@OrderString nvarchar(
200),
--排序字段(支持多字段不用加order by)
@OrderType nvarchar(
4)
=‘asc‘,
--排序类型 (ASC DESC)
@WhereString nvarchar(
500)
=N
‘‘,
--条件语句(不用加where)
@PageSize int,
--每页多少条记录
@PageIndex int = 1 ,
--指定当前为第几页
@TotalRecord int output ,
--返回总记录数
@ErrorMsg nvarchar(
500) output
--返回错误消息
)
as
begin
begin try
declare @StartRecord int;
declare @EndRecord int;
declare @TotalCountSql nvarchar(
1000);
declare @SqlString nvarchar(
2000);
set @StartRecord = (
@PageIndex-1)
*@PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
set @TotalCountSql= N
‘select @TotalRecord = count(*) from ‘ + @TableName;
--总记录数语句
set @SqlString = N
‘(select row_number() over (order by ‘+ @OrderString +‘ ‘+@OrderType+‘) as rowId,‘+@ReFieldsStr+‘ from ‘+ @TableName;
if (
@WhereString!
= ‘‘ or @WhereString!=null)
begin
set @TotalCountSql=@TotalCountSql + ‘ where ‘+ @WhereString;
set @SqlString =@SqlString+ ‘ where ‘+ @WhereString;
end
--第一次执行得到
if(
@TotalRecord is null)
begin
exec sp_executesql
@TotalCountSql,N
‘@TotalRecord int out‘,
@TotalRecord output;
end
----执行主语句
set @SqlString =‘select * from ‘ + @SqlString + ‘) as t where rowId between ‘ + ltrim(
str(
@StartRecord))
+ ‘ and ‘ + ltrim(
str(
@EndRecord));
exec(
@SqlString)
end try
begin catch
set @ErrorMsg = ERROR_MESSAGE()
end catch
end
declare @total int
declare @msg nvarchar(100)
exec proc_getpaging
‘Ritems,honor‘,‘Ritems.*‘,‘Ritems.itemname‘,‘asc‘,‘‘,2,1,@total output ,@msg output
print @total
print @msg
sql 分页rownumber方式
标签：
