Set SQL Variable with a series of IF statements

Question

I have a table which compares certain columns to columns in another table. If a column has changed, I want to ultimately change a "Notes" field to alert what has changed. More than one column could've changed.

In a nutshell, I declare the variable and I want to update that variable with whatever has changed and then Update a column with the variable.

In Access, I can do this quite easily, but I've spent two days trying to accomplish this in SQL to no avail.

Declare @NewUpdate nvarchar(max)

If B.RCustNmbr <> A.RSQ_Custnmbr Set @NewUpdate = 'CustNmbr changed from ' & A.RSQ_Custnmbr & ' to ' & B.RCustNmbr & ', '

If B.RCustName <> A.RSQ_CUSTNAME Set @NewUpdate = **@NewUpdate** & 'CustName changed from ' & A.RSQ_CUSTNAME & ' to ' & B.RCustName & ', ' 

Update A
Set A.[Notes] = @NewUpdate & " on today's date " & A.[Notes] *(to include notes that might've been there before)*
From [Table1] A Inner Join  [Table2] B on A.ID = B.ID

Then I would want to Reset the @NewUpdate variable for the next ID (so Set @NewUpdate = Null)

Can you point me in the right direction to get this accomplished? I'm really unsure how I should reference my A & B tables in the If statements. I've tried a number of things. This is the closest:

Declare @NewUpdate nvarchar(max)

Set @NewUpdate = (Select 'CustName changed from ' + A.RSQ_CustName + ' to ' + B.RCustName + ', '
From [Customer Master List] A INNER JOIN [Customer Master List Changes TEMP] B ON A.ID = B.ID
Where **A.ID = 566** and B.RCustName <> A.RSQ_CustName)

(the above query formatted):

Declare @NewUpdate nvarchar(max)

Set @NewUpdate = 
   (Select 'CustName changed from ' + A.RSQ_CustName + ' to ' + B.RCustName + ', '
      From [Customer Master List] A
INNER JOIN [Customer Master List Changes TEMP] B ON A.ID = B.ID
Where A.ID = 566 and B.RCustName <> A.RSQ_CustName)

But I have to limit it to 1 ID or I get the error Subquery returned more than 1 value. This is not permitted when the subquery follows =, etc.... and I need to do this for thousands of records.

Have you looked at `CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ... ELSE value_default END1` ? — O. Jones, Jul 21 '16 at 17:15
What flavor of SQL are you using? Microsoft SQL (TSQL), Postgres, MySQL, Oracle (PLSQL)? There are CASE statements in SQL that could benefit you... but different variants of SQL might have other options (e.g. Triggers) — Paurian, Jul 21 '16 at 17:16
Thank you for your replies. I have looked into Case When, but didn't have much luck getting that to work with Setting the variable. I tried those first, since I have used them in other code. Yes, I am using Microsoft SQL (TSQL). — Josetta Caudill, Jul 21 '16 at 18:27

vr8ce · Answer 1 · 2016-08-30T17:34:05.103

1

Assuming the two tables have a common key column, you can use a query like this to identify the changes. You'll have to use CASTs if the columns aren't character-based, but this gives you the basic idea.

SELECT d.*
  FROM (SELECT a.key,
               CASE WHEN a.col1<>b.col1
                 THEN 'col1 changed from ' + a.col1 + ' to ' + b.col1 + ','
                 ELSE ''
               END 
               + CASE WHEN a.col2<>b.col2
                   THEN 'col2 changed from ' + a.col2 + ' to ' + b.col2 + ','
                   ELSE ''
                 END AS diff
          FROM a INNER JOIN b ON a.key = b.key) d
 WHERE d.diff <> ''

You can then use the above as its own subquery to join to the table with the note column in it; it sounded like the note column was in one of the original tables.

UPDATE a
   SET a.note = n.diff
  FROM a INNER JOIN (<above select>) n ON a.key = n.key

edited Aug 30 '16 at 17:34

answered Jul 28 '16 at 03:25

vr8ce

476
2
13

Hmmm...this looks interesting. I'm going to try this, too, when I get back to this problem. Thanks! – Josetta Caudill Jul 28 '16 at 14:32
This worked perfectly except during the update...Instead of d.diff, I changed to n.diff. So... UPDATE a SET a.note = n.diff FROM a INNER JOIN () n ON a.key = n.key – Josetta Caudill Aug 10 '16 at 20:38
1

Oops, sorry about that, brought it over from the SELECT. I fixed it for others reading the solution. – vr8ce Aug 30 '16 at 17:34

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

Use UPDATE FROM

UPDATE Table
SET Notes = R.Notes
   From
(
    Select 
         A.TableId
        'CustName changed from ' + A.RSQ_CustName + ' to ' + B.RCustName + ', ' AS Notes
    From 
        [Customer Master List] A INNER JOIN 
        [Customer Master List Changes TEMP] B ON A.ID = B.ID
    Where 
        A.ID = 566 and 
        B.RCustName <> A.RSQ_CustName
) R
WHERE
    Table.Id = R.TableId

edited May 23 '17 at 12:22

Community

1
1

answered Jul 21 '16 at 18:41

neer

4,031
6
20
34

This looks like it can work well, but what if I want to do this for thousands of records (in other words, I don't want the Where clause for ID)? I'm going to take a look at this when I get back to this problem. I'm handling it in Access at the moment. Thanks for your help! – Josetta Caudill Jul 28 '16 at 14:31

Set SQL Variable with a series of IF statements

2 Answers2