Batch update data traversal data

Declare @ ordertable table (RID int identity (1, 1), orderid INT)
Declare @ v_count Int = 0
Insert into @ ordertable (orderid)
Select ID
From t_omsonlineorder t
Where T. orderstatus in (1, 2, 3, 4) ---- query the data to be traversed

Select @ v_count = count (1)
From @ ordertable

While (@ v_count> 0)
Begin

Declare @ orderid Int = 0

Select @ orderid = orderid
From @ ordertable
Where [email protected] _ count -- traverse data

Declare @ omsonlineorderid int,
@ Allnum int,
@ Allsku varchar (max ),
@ Allstoresite varchar (max ),
@ Isgift bit,
@ Standardweight decimal (14,4)

Set @ [email protected]

-- Recalculates the number of items, all SKU names, whether it is a gift or weight.
Select @ allnum = isnull (sum (Num-isnull (refundqty, 0), 0)
, @ Allsku = stuff (select ',' + SKU + '(' + convert (varchar, isnull (sp. num, 0)-isnull (sp. refundqty, 0) + ')' from DBO. t_omsonlineorderproduct SP with (nolock) Where sp. omsonlineorderid [email protected] and sp. refundstatus <> 6 for XML Path (''), 1, 1 ,''),
@ Allstoresite = stuff (select ',' + storesite from DBO. t_omsonlineorderproduct SP with (nolock) Where sp. omsonlineorderid = @ orderid and isnull (sp. storesite, '') <>'' and sp. refundstatus <> 6 for XML Path (''), 1, 1 ,'')
, @ Isgift = case when sum (convert (INT, isnull (isgift, 0) = 0 then 0 else 1 end
, @ Standardweight = sum (standardweight * (Num-isnull (refundqty, 0 )))
From DBO. t_omsonlineorderproduct T with (nolock) Where [email protected] and T. refundstatus <> 6

Set @ allsku = substring (@ allsku, 0,255)

Set @ allstoresite = substring (@ allstoresite, 0,255)


Update t_omsonlineorder
Set [email protected]
Where [email protected]

 

Set @ [email protected] _ count-1 ---- prevent endless Loops
End

 

Batch update data traversal data