sql server - Set SQL Variable with a series of IF statements -

i have table compares columns columns in table. if column has changed, want change "notes" field alert has changed. more 1 column could've changed.

in nutshell, declare variable , want update variable whatever has changed , update column variable.

in access, can quite easily, i've spent 2 days trying accomplish in sql no avail.

---

declare @newupdate nvarchar(max)  if b.rcustnmbr <> a.rsq_custnmbr set @newupdate = 'custnmbr changed ' & a.rsq_custnmbr & ' ' & b.rcustnmbr & ', '  if b.rcustname <> a.rsq_custname set @newupdate = **@newupdate** & 'custname changed ' & a.rsq_custname & ' ' & b.rcustname & ', '   update set a.[notes] = @newupdate & " on today's date " & a.[notes] *(to include notes might've been there before)* [table1] inner join  [table2] b on a.id = b.id 

then want reset @newupdate variable next id (so set @newupdate = null)

---

can point me in right direction accomplished? i'm unsure how should reference & b tables in if statements. i've tried number of things. closest:

---

declare @newupdate nvarchar(max)  set @newupdate = (select 'custname changed ' + a.rsq_custname + ' ' + b.rcustname + ', ' [customer master list] inner join [customer master list changes temp] b on a.id = b.id **a.id = 566** , b.rcustname <> a.rsq_custname) 

---

(the above query formatted):

declare @newupdate nvarchar(max)  set @newupdate =     (select 'custname changed ' + a.rsq_custname + ' ' + b.rcustname + ', '       [customer master list] inner join [customer master list changes temp] b on a.id = b.id a.id = 566 , b.rcustname <> a.rsq_custname) 

but have limit 1 id or error subquery returned more 1 value. not permitted when subquery follows =, etc.... , need thousands of records.

assuming 2 tables have common key column, can use query identify changes. you'll have use casts if columns aren't character-based, gives basic idea.

select d.*   (select a.key,                case when a.col1<>b.col1                  'col1 changed ' + a.col1 + ' ' + b.col1 + ','                  else ''                end                 + case when a.col2<>b.col2                    'col2 changed ' + a.col2 + ' ' + b.col2 + ','                    else ''                  end diff           inner join b on a.key = b.key) d  d.diff <> '' 

you can use above own subquery join table note column in it; sounded note column in 1 of original tables.

update    set a.note = n.diff   inner join (<above select>) n on a.key = n.key