ALTER proc [dbo].[batchCreateVoucherNo]
@czlx nvarchar(50),--操作类型
@dfgy nvarchar(50),--对方柜员
@userid int,--考生ID
@examid int,
@taskid int,
@planid int,
@formid nvarchar(20),
@banksiteid int
as
declare @v_sql nvarchar(max);--凭证号sql
declare @model_count int;
select @model_count=COUNT(*) from tb_daily_voucher where planid = 21
declare @i int;
set @i=1;
while(@i<=@model_count)
begin
declare @pzlx nvarchar(50);--凭证类型
declare @qshm nvarchar(50);--起始号码
declare @zzhm nvarchar(50);--终止号码
declare @pzsl int;--凭证数量
select @pzlx=pzlx,@qshm=qshm,@zzhm=zzhm,@pzsl=pzsl from(select ROW_NUMBER() over(order by id) as rownumber, * from tb_daily_voucher where planid = 21) temp where temp.rownumber=@i
set @v_sql= 'insert into yw_050504(sle_czlx,sle_dfgy,sle_pzzl,txt_qshm,txt_zzhm,txt_pzsl,Operator,AddTime,UserId,ExamId,TaskId,PlanId) values (''' + @czlx + ''',''' + @dfgy + ''','''+@pzlx+''','''+@qshm+''','''+@zzhm+''','+ CAST(@pzsl as nvarchar(10))+',null,GETDATE(),'+ CAST( @userid as nvarchar(10))+','+CAST( @examid as nvarchar(10))+','+CAST( @taskid as nvarchar(10))+','+CAST( @planid as nvarchar(10))+');';
exec(@v_sql);
--print @v_sql;
declare @voucher_font nvarchar(50);
set @voucher_font = SUBSTRING(@qshm,0,LEN(@qshm)-5);--凭证号码前半部分
declare @v_item_sql nvarchar(max);
declare @start int;--起始号码后6位
declare @end int; --终止号码后6位
set @start=SUBSTRING(@qshm,LEN(@qshm)-5,6);
set @end=SUBSTRING(@zzhm,LEN(@zzhm)-5,6);
while(@start<=@end)
begin
set @v_item_sql = 'insert into zhyw_FormVoucherNo(FormId,VoucherType,VoucherNo,BankSiteId,UserId,PlanId,TaskId,ExamId,CreateDate,[Status]) values('''+@formid+''','''+@pzlx+''','''+@voucher_font+right(replicate('0',20)+ltrim(CAST(@start as nvarchar(10))),6)+''','+CAST(@banksiteid as nvarchar(10))+','+CAST(@userid as nvarchar(10))+','+CAST(@planid as nvarchar(10))+','+CAST(@taskid as nvarchar(10))+','+CAST(@examid as nvarchar(10))+',GETDATE(),''未使用'');';
exec(@v_item_sql);
--print @v_item_sql;
set @start=@start+1;
end
set @i=@i+1;
end