使用大小写表达式更新变量，并使用该变量更新 SQL Server 中的表

Question

我正在尝试更新我们的运输订单的状态。它可以编译，但在执行该过程时我没有返回任何状态。

ALTER PROCEDURE [Shipping].[UpdateShippingStatus_ERICA]
@OrderID int = 0
AS
BEGIN
declare @ShipStatus int = 0
declare @TagsVerified varchar(10) 
declare @TagCount int
declare @Cash Varchar
declare @Payment int
declare @VehicleType varchar(10)
declare @LoadedUnits varchar
declare @LoadedWeight int
select @TagCount = count(TGTAGID) from [IBM].[S10F8DCP].SALFIL.TAGCUR where TGORDER = @OrderID 
select @TagsVerified = Y6PRS09, @VehicleType = Y6CAR, @LoadedUnits = Y6PRS09, @LoadedWeight = Y6WGROSS
from [IBM].[S10F8DCP].SALFIL.SORDHAD where Y6ONO = @OrderID
select @Cash = OHOCSH, @Payment = OHCASH from [IBM].[S10F8DCP].SALFIL.SORDH a where OHONO = @OrderID
Select  @ShipStatus = 
(Case
When @ShipStatus = 3 and @Cash = 'N' and @TagsVerified like '%Y%' and @TagCount = (select a.TagsLoaded from sales.IBM.OrdersShipping a where a.OrderID = @OrderID)
Then 4
When @ShipStatus = 3 and @Cash = 'Y' and  @Payment > '0' and @TagsVerified like '%Y%' and @TagCount = (select a.TagsLoaded from sales.IBM.OrdersShipping a where a.OrderID = '35632')
Then 4  
When @ShipStatus = 2 and  @LoadedUnits > '0' and @LoadedWeight > '0' 
Then 3 
When @ShipStatus = 1 and @VehicleType = 'VAN' and exists(select * from [IBM].[S10F8DCP].SALFIL.TRINSP where Y7ONO = @OrderID)
Then 2 
When exists(select * from [IBM].[S10F8DCP].SALFIL.SORDHAD where Y6LDDATE > '0' and Y6CAR <> 'VAN' and Y6ONO = @OrderID)
Then 2
When exists(select * from [IBM].[S10F8DCP].SALFIL.SORDHAD where Y6LDDATE > '0' and Y6CAR = 'VAN' and Y6ONO = @OrderID)
Then 1
End)
FROM sales.IBM.OrdersShipping a
join [IBM].[S10F8DCP].SALFIL.SORDHAD b  on b.Y6ONO = a.OrderId
join [IBM].[S10F8DCP].SALFIL.SORDH c on c.OHONO = b.Y6ONO
join [IBM].[S10F8DCP].SALFIL.TAGCUR d on d.TGORDER = c.OHONO
where OrderID = @OrderID
Update sales.ibm.ordersshipping 
set sales.ibm.ordersshipping.ShippingStatus = 
(Case 
When @ShipStatus = 1 Then 'READYINSP'
When @ShipStatus = 2 Then 'READYLOAD'
When @ShipStatus = 3 Then 'READYSCAN'
When @ShipStatus = 4 Then 'READYSHIP'
END)
where OrderID = @OrderID
END
GO

Answer 1

这是我开始工作的代码。通过"返回"，我的意思是表没有使用当前加载状态进行更新。就是现在。

ALTER PROCEDURE [Shipping].[UpdateShippingStatus_ERICA]
@OrderID int = ''
AS
BEGIN
declare @ShipStatus int
declare @TagsVerified varchar(4)
declare @TagCount int
declare @Cash Varchar(15)
declare @Payment int
declare @VehicleType varchar(5)
declare @LoadedUnits int
declare @LoadedWeight int
declare @Shipped Varchar(1)
declare @OHOSTS Varchar(1)
declare @LoadDate int
select @TagCount = count(TGTAGID) from [IBM].[S10F8DCP].SALFIL.TAGCUR where TGORDER = @OrderID 
select @LoadDate = Y6LDDATE, @TagsVerified = Y6PRS09, @VehicleType = Y6CAR, @LoadedUnits = Y6PRS09, @LoadedWeight = Y6WGROSS
from [IBM].[S10F8DCP].SALFIL.SORDHAD where Y6ONO = @OrderID
select @OHOSTS = OHOSTS, @Shipped = OHSHIP, @Cash = OHOCSH, @Payment = OHCASH from [IBM].[S10F8DCP].SALFIL.SORDH a where OHONO = @OrderID
Select  @ShipStatus = 
(Case
When @Shipped = 'S' and OHOSTS = 'C' Then 7
When @Shipped = 'S' and OHOSTS = 'O' Then 6
When @Cash = 'N' and @TagsVerified like '%Y%' and @TagCount = (select a.TagsLoaded from sales.IBM.OrdersShipping a where a.OrderID = @OrderID)
Then 4
When @Cash = 'Y' and  @Payment > '0' and @TagsVerified like '%Y%' and @TagCount = (select a.TagsLoaded from sales.IBM.OrdersShipping a where a.OrderID = '35632')
Then 4  
When @LoadedUnits > '0' and @LoadedWeight > '0' 
Then 3 
When @VehicleType = 'VAN' and exists(select * from [IBM].[S10F8DCP].SALFIL.TRINSP where Y7ONO = @OrderID)
Then 2 
When @LoadDate > '0' and @VehicleType <> 'VAN'
Then 2
When @LoadDate > '0' and @VehicleType = 'VAN' 
Then 1
Else 5
End)
FROM sales.IBM.OrdersShipping a
join [IBM].[S10F8DCP].SALFIL.SORDHAD b  on b.Y6ONO = a.OrderId
join [IBM].[S10F8DCP].SALFIL.SORDH c on c.OHONO = b.Y6ONO
join [IBM].[S10F8DCP].SALFIL.TAGCUR d on d.TGORDER = c.OHONO
Update sales.ibm.ordersshipping 
set sales.ibm.ordersshipping.ShippingStatus = 
(Case 
When @ShipStatus = 1 Then 'READYINSP'
When @ShipStatus = 2 Then 'READYLOAD'
When @ShipStatus = 3 Then 'READYSCAN'
When @ShipStatus = 4 Then 'READYSHIP'
When @ShipStatus = 5 Then 'NOTARRIVED'
When @ShipStatus = 6 Then 'SHIPPED'
When @ShipStatus = 7 Then 'COMPLETE'
END)
where OrderID = @OrderID
END
GO