Run multiple update statements on same table in stored procedure using values from table vlued param-CodePudding

I want to update a column in a table uing values from a table valued parameter, then I want to update another column (same rows) depending on the value of the column that was updated on first statement. Here is the code:

@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

The table parameter contain two columns (appointmentID, D_report). I guess I can use single update statement using IIF, I'm not sure about the best way to do this.

Thank you.

CodePudding user response：

Depending on what you want WantSMS to become if D_Report is something else, or NULL, or if it should depend on the existing value in the table, you can do this in one statement as follows:

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 is just fancy CASE but I find CASE a lot more flexible. YMMV.