分页存储过程

–车辆分页
create proc proc_GetPagedTruckList2
@number nvarchar(32),–车辆编号
@type nvarchar(32),–车辆类型
@page int,
@pagesize int,
@totalCount int output
as
declare @strsql nvarchar(200);
declare @strwhere nvarchar(200);
set @strwhere=’’;
set @strsql=‘select @rowcount=count(*) from Truck where IsDelete=0’

if(@number<>’’ and @number is not null)
set @strwhere=@strwhere+’ and Number like’’%’+@number+’%’’’;

if(@type<>’’ and @type is not null)
set @strwhere=@strwhere+’ and Type like’’%’+@type+’%’’’;

set @strsql=@strsql+@strwhere;

exec sp_executesql @strsql,N’@rowcount int output’,@totalCount output;

set @strsql= ‘select * from (select row_number() over(order by TruckID) rowIndex, * from Truck where IsDelete=0) t
where t.rowIndex>(@page-1)@pagesize and t.rowIndex<@page@pagesize+1’

set @strsql=@strsql+@strwhere;

exec sp_executesql @strsql,N’@page int,@pagesize int’,@page,@pagesize

go

–查询
declare @totalcount int;
exec proc_GetPagedTruckList2 ‘’,’’,3,4,@totalcount output
print @totalcount
go