title: Create a list of dynamic queries from an array in SQL Server 2008
body: <p>I split this question of from <a href="https://stackoverflow.com/questions/24184180/fill-dynamic-array-with-values-from-an-array/24185288">Fill dynamic array with values from an array</a>. Below is the actual code I now have. </p>
  
  <p>First the dynamic code, second the 3 example queries that I would like the dynamic code to output (but which it doesn't). I get an error <code>Invalid column name "field"</code> or, when I change <code>field</code> into <code>#tmp_auditlog_fields.field</code>, the error that <code>the column "field" cannot be bound</code>. The latter error is probably due to me not joining any tables but I have no clue on which field to join the tables since the <code>#tmp_auditlogs_fields</code> is nothing more than an array to create a bunch of queries, unrelated to any real data in the database.</p>
  
  <p>How can I get the desired output? Any help is greatly appreciated :-)</p>
  
  <pre><code>-- dynamic code
  
  create table #tmp_auditlog_fields (
    id int identity(1,1),
    field varchar(255),
    details varchar(255)
  );
  
  insert into #tmp_auditlog_fields (field, details) values
      ('a', 'foo'),
      ('b', 'bar'),
      ('c', 'baz'),
  ;
  
  declare @sql nvarchar(max);
  declare @cnt int, @i int = 1;
  
  select @cnt = max(id) from #tmp_auditlog_fields;
  
  while @i &lt;= @cnt
      begin
          select @sql = 
              'select t.objectID, t.DocID as ' + field + 
              ' into #tmp_auditlog_' + field + 
              ' from #tmp_auditlog_subselection t where Details like ' + 
              details FROM  tmp_auditlog_fields WHERE id = @i
          exec sp_executesql @sql
          select @i = @i + 1
      end 
  ;
  
  -- example queries
  
  select t.ObjectID, t.DocID as a 
  into #tmp_auditlog_a from #tmp_auditlog_subselection t
  where Details like 'foo';
  
  select t.ObjectID, t.DocID as b 
  into #tmp_auditlog_b from #tmp_auditlog_subselection t
  where Details like 'bar';
  
  select t.ObjectID, t.DocID as c 
  into #tmp_auditlog_c from #tmp_auditlog_subselection t
  where Details like 'baz';
  </code></pre>
  
  <p>SQL Fiddle attempt:</p>
  
  <pre><code>declare @sql nvarchar(max);
  declare @id int;
  
  create table #tmp_auditlog_subselection (
    stuff varchar(255),
    other varchar(255)
  );
  
  insert into #tmp_auditlog_subselection (stuff, other) values
    ('foo', 'bar'),
    ('baz', 'bat'),
    ('lorem', 'ipsum')
  ;
  
  create table #tmp_auditlog_fields (
    id int identity(1,1),
    field varchar(255),
    details varchar(255)
  );
  
  insert into #tmp_auditlog_fields (field, details) values
    ('a', 'foo'),
    ('b', 'bar'),
    ('c', 'baz')
  ;
  
  while exists(select * from #tmp_auditlog_fields where id &gt;= @id)
      begin
          select @sql = 
              'select t.objectID, t.DocID as ' + field + 
              ' into #tmp_auditlog_' + field + 
              ' from #tmp_auditlog_subselection t where Details like ' + 
              details FROM tmp_auditlog_fields WHERE id = @i;
          exec @sql
          select @id = min(id) from #tmp_auditlog_fields where id &gt; @id;
      end 
  ;
  
  select * from #tmp_auditlog_a;
  </code></pre>