使用表vlued参数中的值对存储过程中的同一个表运行多个更新语句

Question

我想用表值参数的值更新表中的一列，然后根据第一条语句中更新的列的值更新另一列(相同的行(。这是代码：

@reports as WhatsTableType ReadOnly
BEGIN
update dbo.tblappointments Set dbo.tblappointments.D_report  = r.D_report
from dbo.tblappointments as A inner join @reports as r on A.appointmentID = r.appointmentID;

update dbo.tblappointments Set dbo.tblappointments.WantSMS  = 0
from dbo.tblappointments as A inner join @reports as r on A.appointmentID = r.appointmentID
where A.D_report = 'Read' or A.D_report = 'DEVICE';

END

表参数包含两列(appointmentID、D_report(。我想我可以使用IIF使用单个更新语句，我不确定最好的方法。

谢谢。

Answer 1

如果D_Report是其他值或NULL，或者它应该取决于表中的现有值，根据您希望WantSMS变成什么，您可以在一条语句中这样做：

UPDATE A Set 
A.D_report = r.D_report,
A.WantSMS = CASE 
-- need to check R for the _new_ value:
WHEN r.D_Report IN ('Read', 'DEVICE') THEN 0 
ELSE A.WantSMS END
FROM dbo.tblappointments as A 
INNER JOIN @reports as r 
ON A.appointmentID = r.appointmentID;

IIF只是一个花哨的CASE，但我发现CASE要灵活得多。YMMV。