存储过程分页,带参,返回值

create PROCEDURE [dbo].[nb_arrearageListByCar] @pageIndexINT,--第几页@pageSizeINT,--分页大小@carNumberNVARCHAR(6),--车牌号@RstatusINT OUTPUT,--返回标识号 0 成功 @RcodeINT OUTPUT,--返回错误编码@RmsgVARCHAR(128) OUTPUT--返回描述状态说明标识ASdeclare @retint--临时参数DECLARE @SQLVARCHAR(MAX)beginbegin try-->step 1 | 空值检测 |if(@pageIndex is null or 0 = @pageIndex or @pageSize is null or 0 = @pageSize )beginset @Rstatus = 1set @Rcode = 4000set @Rmsg = '参数空'select -1 rn,'' code,'' startDate ,-1 reMoney, -1 lastMoneyreturnend--条件筛选declare @sql_where nvarchar(max)declare @start1 nvarchar(16)declare @end1 nvarchar(16)set @sql_where = ''set @start1 = ''set @end1 = ''set @start1 = cast((@pageSize * (@pageIndex-1) + 1) as varchar(16))set @end1 =  cast(@pageSize* @pageIndex as varchar(16))if('' != @carNumber and @carNumber is not null)beginset @sql_where = @sql_where + N' and b.code = '''+ @carNumber +''' 'end-->step 2 | 分页数据 |declare @count1intset @count1 = 0declare @t_sql nvarchar(max)set @t_sql = N'select @c = count(1)from (select a.parkOrderID,sum(isnull(c.orderMoney,0)) reMoney from parkOrder a left join parkSpace b on b.spaceID = a.spaceID left join orderPayBank c on c.Orderid = a.parkOrderIDwhere a.totalMoney - a.reMoney > 0 and a.spaceStatus = 2 ' + @sql_where + 'group by a.parkOrderID) aleft join parkOrder b on b.parkOrderID = a.parkOrderIDleft join parkSpace c on c.spaceID = b.spaceIDwhere 1 = 1'exec  sp_executesql  @t_sql,N'@c int output', @count1 outputset @t_sql = ''if(0 = @count1)beginset @Rstatus = 1set @Rcode = 0set @Rmsg = '没有欠款记录'select -1 rn,'' code,'' startDate ,-1 reMoney, -1 lastMoneyreturnendelsebeginset @Rcode = @count1set @t_sql = N'select * from (select ROW_NUMBER() over (order by c.code) rn,b.parkOrderID,c.code,convert(char(20),b.startDate,120) startDate,convert(char(20),b.endDate,120) endDate,LTRIM(RTRIM(b.carNumber)) carNumber,DATEDIFF(MINUTE,b.startDate,b.endDate) howlong,a.reMoney,b.totalMoney - a.reMoney lastMoneyfrom (select a.parkOrderID,sum(isnull(c.orderMoney,0)) reMoney from parkOrder a left join parkSpace b on b.spaceID = a.spaceID left join orderPayBank c on c.Orderid = a.parkOrderIDwhere  a.totalMoney - a.reMoney > 0 and a.spaceStatus = 2 ' + @sql_where + 'group by a.parkOrderID) aleft join parkOrder b on b.parkOrderID = a.parkOrderIDleft join parkSpace c on c.spaceID = b.spaceIDwhere 1 = 1 ) Ch where Ch.rn between ' + @start1 + ' and ' + @end1exec  sp_executesql  @t_sqlset @Rstatus = 0set @Rmsg = '获取成功'endend trybegin catchset @Rstatus = -1set @Rcode = -1set @Rmsg = ERROR_MESSAGE()insert into parkErr(proName,proTime,proErr)values('[nb_arrearageList]',GETDATE(),ERROR_MESSAGE())end catchend