SQL存储过程处理JSON字符串及循环控制

USE [DZTWMS]
GO
/****** Object:  StoredProcedure [dbo].[JobTmpAPIDataGet]    Script Date: 2019/8/2 12:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*
对传入接口TmpAPIData的数据进行处理
数据类型：0商品分类；1销售单；2退货单；3退厂单；4采购单；5盘库单； 7客户；8供应商；9商品基础；10商品包装；11销售单撤销;12退货单撤销；13退厂撤销；14采购撤销；15 盘库单撤销；
            --210,销售单回传-已取消 211,销售单回传-已同步  212,销售单回传-装车完成发货中 213,销售单回传-已完成220,退货单回传-已取消  221,司机带回商品回传230,退厂单回传-已取消  231,退厂单回传-完成 240,采购单回传-已取消  241,采购单回传-验货完成 250,盘库单回传-已取消   251,盘库单回传-完成 26,报损回传
*/

ALTER PROC [dbo].[JobTmpAPIDataGet]
AS


BEGIN TRY
BEGIN TRAN [JobTmpAPIDataGet]

DECLARE @result int,@tipMsg varchar(100),@result2 int,@tipMsg2 varchar(100),@cnt int
DECLARE @n int,@rows int
DECLARE @Id int,@DataType int=0,@SheetId int,@OwnerId int,@APIData varchar(max),@WareHouseId int,@DetailList varchar(7000)
create table #tmp_TmpAPIData(num int IDENTITY(1,1),Id int,DataType int,OwnerId int,WareHouseId int,SheetId int,APIData varchar(max))
INSERT INTO #tmp_TmpAPIData(Id,DataType,OwnerId,WareHouseId,SheetId,APIData) 
select top 500 Id,DataType,OwnerId,WareHouseId,SheetId,APIData
from TmpAPIData where OutIn=1 and Status=0  order by DataType 
SELECT @rows =@@rowcount 

set @n=1 
while @n<=@rows
BEGIN
SELECT @Id=Id,@APIData=APIData,@OwnerId=OwnerId,@SheetId=SheetId,@WareHouseId=WareHouseId,@DataType=DataType 
FROM  #tmp_TmpAPIData WHERE num=@n

--定义一些共同的变量
DECLARE @WareHouseName varchar(50),@OwnerName varchar(50),@Name varchar(250),@Remark nvarchar(1000),@OrderId varchar(20),@Status int,
@CustomNo varchar(20),@CustomName varchar(150),@Phone char(15),@PCANames nvarchar(300),@Addr nvarchar(200),@SalesMan varchar(20),@SalesMobile char(15),@OrderNum varchar(150),
@SupplierNo varchar(20),@Amount decimal(10,2)
SELECT @OwnerName=[Name],@WareHouseName=WareHouseName FROM [Owner] WHERE Id=@OwnerId

--商户订单
if(@DataType=1)
BEGIN 
DECLARE @IsCollect tinyint,@DeliverType tinyint,@CostTotal decimal(10,2),@Total decimal(10,2), @Freight decimal(5),@StartTime tinyint,@EndTime tinyint
select @OrderNum=JSON_VALUE(value,'$.OrderNum'),@IsCollect=JSON_VALUE(value,'$.IsCollect'),@DeliverType=JSON_VALUE(value,'$.DeliverType'),@CustomNo=JSON_VALUE(value,'$.CustomNo'),@CustomName=JSON_VALUE(value,'$.CustomName'),@Phone=JSON_VALUE(value,'$.Phone'),@PCANames=JSON_VALUE(value,'$.PCANames'),@Addr=JSON_VALUE(value,'$.Addr'),@SalesMan=JSON_VALUE(value,'$.SalesMan'),@SalesMobile=JSON_VALUE(value,'$.SalesMobile'),@CostTotal=JSON_VALUE(value,'$.CostTotal'),@Total=JSON_VALUE(value,'$.Total'),@Freight=JSON_VALUE(value,'$.Freight'),@StartTime=JSON_VALUE(value,'$.StartTime'),@EndTime=JSON_VALUE(value,'$.EndTime'),@Remark=JSON_VALUE(value,'$.Remark'),@DetailList=JSON_QUERY(VALUE,'$.ItemList') from openjson(@APIData)
EXEC [AddEditSalesOrder]  @WareHouseId,@OwnerId,@SheetId,@OrderNum,@IsCollect,@DeliverType,@CustomNo,@CustomName,@Phone,@PCANames,@Addr,@SalesMan,@SalesMobile,@CostTotal,@Total,@Freight,@StartTime,@EndTime,@Remark,@result OUTPUT,@tipMsg OUTPUT
if(@result>0) 
begin 
set @OrderId=@SheetId
--DECLARE @ItemBaseId varchar(20),@Spec decimal(10,2),@Unit varchar(4),@Cost decimal(10,2),@Price decimal(10,2),@BuyNum decimal(10,2)
--select @SheetId=JSON_VALUE(value,'$.SheetId'),@ItemBaseId=JSON_VALUE(value,'$.ItemBaseId'),@Spec=JSON_VALUE(value,'$.Spec'),@Unit=JSON_VALUE(value,'$.Unit'),@Cost=JSON_VALUE(value,'$.Cost'),@Price=JSON_VALUE(value,'$.Price'),@BuyNum=JSON_VALUE(value,'$.BuyNum')
select JSON_VALUE(value,'$.SheetId') SheetId,JSON_VALUE(value,'$.ItemBaseId') ItemBaseId,JSON_VALUE(value,'$.Spec') Spec,JSON_VALUE(value,'$.Unit') Unit,JSON_VALUE(value,'$.Cost') Cost,JSON_VALUE(value,'$.Price') Price,JSON_VALUE(value,'$.BuyNum') BuyNum 
into #tmp_saleorderdetail  from openjson(@DetailList)

    INSERT INTO SalesOrderDetail(WareHouseId,OwnerId,SheetId,OrderId,OrderNum,WmsOrderId,ItemBaseId,ItemId,Item,ItemType,Spec,Unit,Cost,Price,BuyNum) 
select @WareHouseId,@OwnerId,a.SheetId,@OrderId,@OrderNum,@result,a.ItemBaseId,b.Id,b.Name,b.ItemType,a.Spec,a.Unit,a.Cost,a.Price,a.BuyNum
from #tmp_saleorderdetail a inner join ItemBase b on a.ItemBaseId=b.SheetId and b.OwnerId=@OwnerId and b.WareHouseId=@WareHouseId