存储过程的问题帮忙解决一下 这是一个动态提交的语句
Alter procedure pr_KeFuFaHuo_FaHuoDan
@CustomerNo varchar(50),
@KeFuFaHuoID varchar(6000),
@CaoZuoRen varchar(50),
@Factory varchar(50)
as
begin
set xact_abort on
begin transaction t
declare @kh_Lxr varchar(50),@kh_Tel varchar(50),@kh_Fx varchar(100),@kh_Dz varchar(100);
declare keCursor cursor for
select top 1 isnull(Dt.DeptName,'客户资料不完整'),isnull(Dt.Tel1,'客户资料不完整'),
isnull(Z.ZoneName,'客户资料不完整'),isnull(C.Address,'客户资料不完整')
from CustomerDept Dt
left join Customer C on Dt.CustomerNo=C.CustomerNo
left join Zone Z on C.City=Z.id
where Dt.DeptFlag='1' and Dt.CustomerNo=@CustomerNo
open keCursor
fetch next from KeCursor into @kh_Lxr,@kh_Tel,@kh_Fx,@kh_Dz
while @@fetch_status=0
begin
break;
end
close keCursor;
deallocate keCursor
declare @vsql varchar(8000);
set @vsql=(' declare @ddlx varchar(50);declare @ddbh varchar(50);declare @fhsl int;'
+' declare @mxddlx varchar(50);declare @mxddbh varchar(50);declare @mxcpbh varchar(50);declare @mxfhsl int;'
+' declare @t table( DingDanLeiXing varchar(50),DingDanBianhao varchar(50),FaHuoShuLiang int);'
+' insert into @t(DingDanLeiXing,DingDanBianhao,FaHuoShuLiang) '
+' select DingDanLeiXing,DingDanBianhao,sum(FaHuoShuLiang) FaHuoShuLiang '
+' from KeFuFaHuo where ID in ('+@KeFuFaHuoID+') group by DingDanLeiXing,DingDanBianhao ; '
+' declare @t_mingxi table(DingDanLeiXing varchar(50),DingDanBianhao varchar(50),ChanPinBianHao varchar(50),FaHuoShuLiang int);'
+' insert into @t_mingxi(DingDanLeiXing,DingDanBianhao,ChanPinBianHao,FaHuoShuLiang) '
+' select DingDanLeiXing,DingDanBianhao,ChanPinBianHao,sum(FaHuoShuLiang) FaHuoShuLiang '
+' from KeFuFaHuo where ID in ('+@KeFuFaHuoID+') group by DingDanLeiXing,DingDanBianhao,ChanPinBianHao ; '
+' declare MyCursor Cursor for select DingDanLeiXing,DingDanBianhao,FaHuoShuLiang from @t '
+' open MyCursor '
+' fetch next from MyCursor into @ddlx,@ddbh,@fhsl '
+' while @@fetch_status=0 '
+' begin '
+' declare @bh_FH varchar(50); '
+' set @bh_FH=( Replace(Replace(Replace(Replace(Convert(varchar(50),GetDate(),21),'':'',''''),''-'',''''),'' '',''''),''.'','''') ) ; '
+' print @bh_FH '
-- +' insert into FaHuoDan(ID,FaHuoDanBianHao,DanJuRiQi,KeHuBianHao,KeHuLianXiRen,KeHuLianXiRenLianXiFangShi, '
-- +' FangXiang,FaHuoDiZhi,FaHuoShuLiang,ZhuangTai,XinJianRen,XinJianRiQi,DeleteTag,ShouHuoQueRen, '
-- +' ChanPinLeiXing,DingDanLeiXing,ShouHuoShuLiang,SuoShuGongChang) '
-- +' values(NewID(),@bh_FH,GetDate(),'''+@CustomerNo +''','''+@kh_Lxr+''','''+@kh_Tel+''','
-- + ' '''+@kh_Fx+''','''+@kh_Dz+''',@fhsl,''0'','''+@CaoZuoRen+''',GetDate(),''0'',''0'', '
-- +' substring(@ddlx,0,1),@ddlx,'''','''+@Factory+''' ) '
-- +' declare MxCursor cursor for select DingDanLeiXing,DingDanBianhao,ChanPinBianHao,FaHuoShuLiang from @t_mingxi '
-- +' open MxCursor '
-- +' fetch next from MxCursor into @mxddlx,@mxddbh,@mxcpbh,@mxfhsl '
-- +' while @@fetch_status=0 '
-- +' begin '
-- +' if @mxddlx=@ddlx and @mxddbh=@ddbh '
-- +' begin '
-- +' insert into FaHuoDanMingXi(id,FaHuoDanBianHao,ChanPinBianHao,ShuLiang)'
-- +' values( NewID(),@bh_FH,@mxcpbh,@mxfhsl ) '
-- +' end '
-- +' fetch next from MxCursor into @mxddlx,@mxddbh,@mxcpbh,@mxfhsl '
-- +' end '
-- +' close MxCursor '
-- +' deallocate MxCursor '
+' fetch next from MyCursor into @ddlx,@ddbh,@fhsl '
+' end '
+' close MyCursor '
+' deallocate MyCursor'
);
print @vsql;
exec (@vsql)
if @@Error<>0
begin
RollBack transaction t;
end
else
begin
Commit transaction t;
end
end
set @bh_FH=( Replace(Replace(Replace(Replace(Convert(varchar(50),GetDate(),21),'':'',''''),''-'',''''),'' '',''''),''.'','''') ) ;
此句为什么获得的时间一样,这是在一个循环里边的应该时间是不一样的啊,各位帮解决下,3ks
---以下是执行语句
begin
declare @CustomerNo varchar(50), @KeFuFaHuoID varchar(500), @CaoZuoRen varchar(50), @Factory varchar(50);
set @KeFuFaHuoID=(' ''3859C7DB-4866-4312-8886-69D42DD69538'',''D0B58016-487E-4E24-81C5-5E4B62383D6B'' ');
exec pr_KeFuFaHuo_FaHuoDan 'J2011006',@KeFuFaHuoID,'777777','001001'
end