SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER procedure p_packOut_check
@voucher varchar(32),@partcode varchar(18), @out_num decimal(10,2)
as
begindeclare @num decimal(10,2)
declare @price1 decimal(18,5)
declare @fax decimal(18,5)
declare @price2 decimal(18,5)
declare @supply_id varchar(10)
declare @supply_cn varchar (15)
declare @raidno varchar (32)while @out_num>0
begin
select top 1 @num=stock_num ,@price1=price1 ,@fax=fax ,@price2=price2 ,@raidno=raidno from pack_fix_in where stock_num>0 and partcode=@partcode order by in_date
if @out_num<=@num
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @out_num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=@num-@out_num where partcode=@partcode and raidno=@raidno
set @out_num=0
end
else
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=0 where partcode=@partcode and raidno=@raidno
set @out_num=@out_num-@num
end
end
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
EXEC [GR_Logistics].[dbo].[p_packOut_check] 'Jl201261601',' 020800700020',3.0
执行时 数据没进行操作
而我直接这样写 有没问题 为什么 ??
declare @voucher varchar(32)
declare @partcode varchar(18)
declare @out_num decimal(10,2)
set @voucher='Jl201261601'
set @partcode='020800700020'
set @out_num='3'
begin
declare @num decimal(10,2)
declare @price1 decimal(18,5)
declare @fax decimal(18,5)
declare @price2 decimal(18,5)
declare @supply_id varchar(10)
declare @supply_cn varchar (15)
declare @raidno varchar (32)
while @out_num>0
begin
select top 1 @num=stock_num ,@price1=price1 ,@fax=fax ,@price2=price2 ,@raidno=raidno from pack_fix_in where stock_num>0 and partcode=@partcode order by in_date
if @out_num<=@num
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @out_num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=@num-@out_num where partcode=@partcode and raidno=@raidno
set @out_num=0
end
else
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=0 where partcode=@partcode and raidno=@raidno
set @out_num=@out_num-@num
end
end
end
GO
SET ANSI_NULLS ON
GOALTER procedure p_packOut_check
@voucher varchar(32),@partcode varchar(18), @out_num decimal(10,2)
as
begindeclare @num decimal(10,2)
declare @price1 decimal(18,5)
declare @fax decimal(18,5)
declare @price2 decimal(18,5)
declare @supply_id varchar(10)
declare @supply_cn varchar (15)
declare @raidno varchar (32)while @out_num>0
begin
select top 1 @num=stock_num ,@price1=price1 ,@fax=fax ,@price2=price2 ,@raidno=raidno from pack_fix_in where stock_num>0 and partcode=@partcode order by in_date
if @out_num<=@num
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @out_num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=@num-@out_num where partcode=@partcode and raidno=@raidno
set @out_num=0
end
else
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=0 where partcode=@partcode and raidno=@raidno
set @out_num=@out_num-@num
end
end
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
EXEC [GR_Logistics].[dbo].[p_packOut_check] 'Jl201261601',' 020800700020',3.0
执行时 数据没进行操作
而我直接这样写 有没问题 为什么 ??
declare @voucher varchar(32)
declare @partcode varchar(18)
declare @out_num decimal(10,2)
set @voucher='Jl201261601'
set @partcode='020800700020'
set @out_num='3'
begin
declare @num decimal(10,2)
declare @price1 decimal(18,5)
declare @fax decimal(18,5)
declare @price2 decimal(18,5)
declare @supply_id varchar(10)
declare @supply_cn varchar (15)
declare @raidno varchar (32)
while @out_num>0
begin
select top 1 @num=stock_num ,@price1=price1 ,@fax=fax ,@price2=price2 ,@raidno=raidno from pack_fix_in where stock_num>0 and partcode=@partcode order by in_date
if @out_num<=@num
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @out_num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=@num-@out_num where partcode=@partcode and raidno=@raidno
set @out_num=0
end
else
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=0 where partcode=@partcode and raidno=@raidno
set @out_num=@out_num-@num
end
end
end
解决方案 »
- 多表union后,如何取出top n的全部数据呢
- 怎么删除ndf文件?
- sql server中sql语句中的逗号如何转意
- UPDATE问题
- 求一简单SQL语句
- ==== 急!! === 系统崩溃,重装后如何从.MDF, .LDF 文件及back up 文件恢复数据库?
- 各位大哥,我有一个关于cursor的问题,想要请教,谢谢了,在线等待
- 把数据库里所有用户表的表名和表的所有字段显示出来,请问SQL语句怎么写?
- 请问我已经安装了SQL server2000,怎么样建一个数据库呢?
- 关于事务控制,还有一点就搞定了,大家帮忙,在线。
- 一台MSSQL服务器,经常CPU过高
- sql2005对硬盘的读写是不是比sql2000高很多。。
直接执行这个呢 ?
执行时 数据没进行操作
而我直接这样写 又没问题 为什么 ??
declare @voucher varchar(32)
你把存储过程的脚本先都注释掉,
只执行部分字段,
尝试 把每一步得到的值 select 或者 print 出来 ...ALTER procedure p_packOut_check
@voucher varchar(32),
@partcode varchar(18),
@out_num decimal(10,2)
as
begin
declare @num decimal(10,2)
declare @price1 decimal(18,5)
declare @fax decimal(18,5)
declare @price2 decimal(18,5)
declare @supply_id varchar(10)
declare @supply_cn varchar (15)
declare @raidno varchar (32) while @out_num>0
begin
---------------------------------------------------------------------------------节点1
select 1
select top 1 @num=stock_num ,@price1=price1 ,@fax=fax ,@price2=price2 ,@raidno=raidno from pack_fix_in where stock_num>0 and partcode=@partcode order by in_date
---------------------------------------------------------------------------------节点2
/*
--脚本每次运行一部分,查看在存储过程里面执行的每一步结果
select @num,@price1,@fax,@price2,@raidno
if @out_num<=@num
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @out_num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=@num-@out_num where partcode=@partcode and raidno=@raidno
set @out_num=0
end
else
begin
insert into pack_fix_out_details ([voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], [out_num], [out_date], [price1], [fax], [price2], [supply_id], [supply_cn], [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes])
select [voucher], [raidno], [partcode], [partname], [parttype], [partunit], [partclass], @num, [out_date],@price1, @fax, @price2, @supply_id,@supply_cn, [hander], [maker], [makedate], [checker], [checkdate], [car_no], [cn], [notes] from pack_fix_out
where voucher=@voucher and partcode=@partcode
update pack_fix_in set stock_num=0 where partcode=@partcode and raidno=@raidno
set @out_num=@out_num-@num
end
*/
end
end