Run multiple update statements on same table in stored procedure using values from table vlued parameter

user_10099116 0
Viewed 54

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.

stored-procedures sql-server sql
1 Answers

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.

user_613050
Related