title: SQL Update Dynamic Based on Table?
body: <p>I am trying to think through a way to do some rule-based SQL Update statements on a table. 
  Things look like the below, which IMO is not really optimal:</p>
  
  <pre><code>create table #typeofchange ( TypeOfChangeId Int  , ChangeNote VarChar(16) )
  Insert into #typeofchange values ( 0 , 'prepend' )
  Insert into #typeofchange values ( 1 , 'replace' )
  Insert into #typeofchange values ( 2 , 'append' )
  create table #dynamicstuff ( DynColName VarChar(16) , RowValue VarChar(16) , TypeOfChangeId Int )
  Insert into #dynamicstuff values ( 'Column_A' , 'xxxx' , 0 )
  Insert into #dynamicstuff values ( 'Column_B' , 'yyyy' , 1 )
  Insert into #dynamicstuff values ( 'Column_C' , 'zzzz' , 2 )
  create table #thetable ( Column_A VarChar(50) , Column_B VarChar(50) , Column_C VarChar(50) )
  insert into #thetable values ( '-1-' , '-2-' , '-3-' )
  insert into #thetable values ( '-4-' , '-5-' , '-6-' )
  insert into #thetable values ( '-7-' , '-8-' , '-9-' )
  
  --      Dynamic transform to get this result set --
  --      Column_A    Column_B    Column_C
  --      xxxx-1-     yyyy        -3-zzzz
  --      xxxx-4-     yyyy        -6-zzzz
  --      xxxx-7-     yyyy        -9-zzzz
  ------------------------------------------------
  </code></pre>
  
  <p>The table schemata do not have to be this way. But I do need to figure out a way to do Update
  statements against #thetable based on rules. I would rather not have to do dynamic SQL because
  then checking return values and debugging is a pain.</p>
  
  <p>Also, the actual #thetable has 144 columns, and can grow, so something with a case statement would be a problem I think.</p>
  
  <p>I thought about changing #dynamicstuff to this, but I think it still walks me into a dynamic SQL trap:</p>
  
  <pre><code>create table #dynamic2 ( Column_A VarChar(50) , Column_B VarChar(50) , Column_C VarChar(50) , TypeOfChangeId , RowValue )
  insert into #dynamic2 values ( 'dothis' , null , null , 0 , 'xxxx' )
  insert into #dynamic2 values ( null , 'dothis' , null , 1 , 'yyyy' )
  insert into #dynamic2 values ( null , null , 'dothis' , 2 , 'zzzz' )
  </code></pre>
  
  <p>Thanks for all ideas.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I think if I drop #dynamic and make a new table called #dynamic2, maybe I can make this work. Here are my tables now:</p>
  
  <pre><code>create table #typeofchange ( TypeOfChangeId Int  , ChangeNote VarChar(16) )
  Insert into #typeofchange values ( 0 , 'prepend' )
  Insert into #typeofchange values ( 1 , 'replace' )
  Insert into #typeofchange values ( 2 , 'append' )
  create table #dynamicstuff2 ( Column_A VarChar(50) , Column_B VarChar(50) , Column_C VarChar(50) , TypeOfChangeId Int )
  insert into #dynamicstuff2 values ( 'xxxx' , null , null , 0 )
  insert into #dynamicstuff2 values ( null , 'yyyy', null , 1 )
  insert into #dynamicstuff2 values ( null , null , 'zzzz' , 2 )
  create table #thetable ( Column_A VarChar(50) , Column_B VarChar(50) , Column_C VarChar(50) )
  insert into #thetable values ( '-1-' , '-2-' , '-3-' )
  insert into #thetable values ( '-4-' , '-5-' , '-6-' )
  insert into #thetable values ( '-7-' , '-8-' , '-9-' )
  </code></pre>
  
  <p>I was thinking that I could somehow make a Union betwen #thetable and #dynamic2 to get everything in a single result set and then update in the table but I think I am still stuck with dynamic SQL.</p>