存储过程：批量出库

最新推荐文章于 2020-08-17 08:54:44 发布

雁过留香

最新推荐文章于 2020-08-17 08:54:44 发布

阅读量312

点赞数

分类专栏： SQL存储过程

本文链接：https://blog.csdn.net/qq_41969460/article/details/87894202

版权

这是一个SQL存储过程，用于处理批量出库操作。通过参数化输入，将多个出库单、商品、订单号、数量等信息拆分并匹配，检查库存，进行数据分配和插入到销售出库单及明细表中。过程涉及到临时表的使用、字符串处理函数以及库存检查逻辑。

摘要由CSDN通过智能技术生成

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--declare @Fgh nvarchar(255),@FBillNO nvarchar(255),@FName nvarchar(255), @FOrderNo nvarchar(255),@FQty nvarchar(255),@FNote nvarchar(500),@FTime nvarchar(255)
--
--set @Fgh = 'GF0641-8-44+17-11B-1#'
--set @FBillNO = 'GF0641-8-44B,GF0641-8-44B,GF0641-17-11B'
--set @FName = 'GF-81212H,GF-81212H-2,GF-81212H-1'
--set @FOrderNo = 'FDW8651,FDW8651,FDW8886-11'
--set @FQty = '84,84,400'
--set @FNote = '测试1,测试2,测试3'
--set @FTime = '2019-03-02'
--exec GF_XOUTStock @Fgh,@FBillNO,@FName,@FOrderNo,@FQty,@FNote

ALTER PROCEDURE [dbo].[GF_XOUTStock](@Fgh nvarchar(255),@FBillNO nvarchar(255),@FName nvarchar(255), @FOrderNo nvarchar(255),@FQty nvarchar(255),@FNote nvarchar(2500),@FTime nvarchar(255))
as
declare @Findex int
select @Findex = len(@FBillNO)-len(replace(@FBillNO,',',''))+1

declare @FDate datetime
select @FDate = FPreDay from t_workcalendar where DATEDIFF(DD, FDay,@FTime)=0

--select @FBillNO,@FName,@FOrderNo,@FQty,@FNote
if object_id('tempdb..#M') is not null Begin
   drop table #M
   End
create table #M(Fgh nvarchar(255),FBillNO nvarchar(255),FName nvarchar(255),FOrderNo nvarchar(255),
FQty int,FNote nvarchar(500),Findex int,FStatus int)
declare @i int
set @i=1
while @i<=@Findex
begin
insert into #M(Fgh,FBillNO,FName,FOrderNo,FQty,FNote,Findex,FStatus)
   select @Fgh,@FBillNO,@FName,@FOrderNo,0,@FNote,@i,0

declare @str varchar(5000)
set @str=@FBillNO
--print dbo.Get_StrArrayLength(@str,',')
declare @next1 int
--declare @s varchar(100)
set @next1=1
while @next1<=dbo.Get_StrArrayLength(@str,',')
begin
update #M set FBillNO = dbo.Get_StrArrayStrOfIndex(@str,',',@next1) where Findex = @next1
-- print dbo.Get_StrArrayStrOfIndex(@str,',',@next) ----输出数组中的值
set @next1=@next1+1
end

set @str=@FName
--print dbo.Get_StrArrayLength(@str,',')
declare @next2 int
--declare @s varchar(100)
set @next2=1
while @next2<=dbo.Get_StrArrayLength(@str,',')
begin
update #M set FName = dbo.Get_StrArrayStrOfIndex(@str,',',@next2) where Findex = @next2
-- print dbo.Get_StrArrayStrOfIndex(@str,',',@next) ----输出数组中的值
set @next2=@next2+1
end

set @str=@FOrderNo
--print dbo.Get_StrArrayLength(@str,',')
declare @next3 int
--declare @s varchar(100)
set @next3=1
while @next3<=dbo.Get_StrArrayLength(@str,',')
begin
update #M set FOrderNo = dbo.Get_StrArrayStrOfIndex(@str,',',@next3) where Findex = @next3
-- print dbo.Get_StrArrayStrOfIndex(@str,',',@next) ----输出数组中的值
set @next3=@next3+1
end

set @str=@FQty
--print dbo.Get_StrArrayLength(@str,',')
decl