如何在Update语句中连接错误信息

Question

我有一个脚本，它执行一些验证，然后用结果更新一些列。在结果中，有一个错误消息列，它是一个人类可读的错误消息。由于可能有多个列无效，因此错误消息可能不同。

我使用变量做了下面的操作，但我认为在这里使用变量不是一个好主意。有更好的方法吗?

declare @ErrorMessage nvarchar(100);
;WITH _UPDATE_ as
(
Select CsrNum 
,Substring(CsrNum, 2, 10) as CdsCsrId 
,IsNumeric(Substring(CsrNum, 2, 10)) as [CdsCsrIdIsNumeric]
,case when IsNumeric(Substring(CsrNum, 2, 10)) = 1 then (Select CsrId from Csr c where c.CdsCsrId = Substring(l.CsrNum, 2, 10)) 
Else null
end as ConvertedCsrId
,PortalCsrid
,PlankLoadStatusId
,ErrorMessage
,PlankLoadStatusDate
,(Select Code from State where Code = l.OrderCust_StateCode) as ValidatedStateCode
from OrdercustPlankLoad l
Where PlankLoadStatusId = 1
)
Update _UPDATE_ set 
@ErrorMessage += case when  ValidatedStateCode is null then 'StateCode is invalid; ' else null end
,@ErrorMessage += case when ConvertedCsrId is null then 'CsrNum is Invalid; ' end
,ErrorMessage = @ErrorMessage
, PortalCsrId = ConvertedCsrId
, PlankLoadStatusId = case when @ErrorMessage is not null 
then 4 --Error
else 2 -- Validated
end
,PlankLoadStatusDate = GetUTCDate()

Answer 1

不如这样试试:

;WITH _UPDATE_ AS (
SELECT 
CsrNum 
, SUBSTRING ( CsrNum, 2, 10 ) AS CdsCsrId 
, ISNUMERIC ( SUBSTRING ( CsrNum, 2, 10 ) ) AS [CdsCsrIdIsNumeric]
, CASE 
WHEN ISNUMERIC ( SUBSTRING ( CsrNum, 2, 10 ) ) = 1 THEN 
( SELECT CsrId FROM Csr c WHERE c.CdsCsrId = SUBSTRING ( l.CsrNum, 2, 10 ) ) 
ELSE NULL
END AS ConvertedCsrId
, PortalCsrid
, PlankLoadStatusId
, ErrorMessage
, PlankLoadStatusDate
, ( SELECT Code FROM [State] WHERE Code = l.OrderCust_StateCode ) AS ValidatedStateCode
FROM OrdercustPlankLoad l
WHERE
PlankLoadStatusId = 1
)
UPDATE _UPDATE_
SET
ErrorMessage = CASE
WHEN ValidatedStateCode IS NULL AND ConvertedCsrId IS NULL THEN 'StateCode is invalid; CsrNum is Invalid;'
WHEN ValidatedStateCode IS NULL THEN 'StateCode is invalid;'
WHEN ConvertedCsrId IS NULL THEN 'CsrNum is Invalid;'
ELSE NULL
END
, PortalCsrId = ConvertedCsrId
, PlankLoadStatusId = CASE
WHEN ValidatedStateCode IS NULL OR ConvertedCsrId IS NULL THEN 4 --Error
ELSE 2 -- Validated
END
, PlankLoadStatusDate = GETUTCDATE();