title: Dynamic T-SQL - Appending parameterized subqueries to a temporary aggregate table
body: <p>The schema of this database is odd; all of the data is broken up by data type and stored in respective tables. When a 'table' needs to be accessed, it needs to be stitched together, fields by table id, values by field id. It's slow and has been largely been taken care of by third party spaghetti scripts.</p>
  
  <p>I have some tables formatted like so:</p>
  
  <p>dbo.TagSource:</p>
  
  <pre><code>TagSourceId | Table Name | ...
  ------------------------------------
  01            Table 1
  02            Table 2
  </code></pre>
  
  <p>dbo.Tag:</p>
  
  <pre><code>TagId | TagSourceId | Field Name | RawTableName | ...
  ------------------------------------------------------
  11      01            Name         dbo.StringValue
  12      01            Age          dbo.IntegerValue
  13      01            Balance      dbo.DecimalValue
  14      01            Document     dbo.FileValue
  15      02            Name         ExternalTable1
  16      02            Height       ExternalTable1
  17      02            Occupation   ExternalTable2
  </code></pre>
  
  <p>dbo.[String/Integer/Decimal/File/...]Value tables:</p>
  
  <pre><code>ValueId | TagId | Value
  -------------------------------
  101       11      John Smith
  102       11      Jane Smith
  103       11      John Doe 
  104       11      Jane Doe
  105       11      Thanos
  </code></pre>
  
  <p><strong>GOAL:</strong> To create a TSQL query script (stored procedure/function/view) that given a TagSourceID as a parameter/declared variable, returns the corresponding table. Eventually, this will be used to create views until I have the bandwidth to gut and replace this DB schema.</p>
  
  <p><strong>What is the optimal way to go about this?</strong> It's challenging since the number of fields in each table is dynamic and the column definitions are unknown.</p>
  
  <p><strong>Attempts:</strong>
  I have tried to use temporary tables to hold my aggregate data and the data to be appended, but soon found that since the column definitions have to be predefined I had to resort to an awkward juggling of data between temporary tables. Seems sub-optimal, even if it worked, which it doesn't.</p>
  
  <p>Logic:</p>
  
  <pre><code>BEGIN LOOP
  #B = #A Join (Select value From dbo...Value Table...)
  Drop Table #A
  #A = #B
  Drop Table #B
  END
  </code></pre>
  
  <p>Script: 
  Returns error <code>There is already an object named '#ResultSet' in the database.</code> even though I've explicitly <code>DROP TABLE #ResultSet</code> the line before the error line.</p>
  
  <pre><code>--*** DECLARATION ***
  USE [DB_Arc];
  GO
  
  -- ***************
  -- Target Table Id
  DECLARE @TagSourceID INT = 130;
  -- ***************
  
  -- Temporary Tables - DROP IF EXIST
  -- #TempA
  IF OBJECT_ID(N'#ResultSet') IS NOT NULL DROP TABLE #ResultSet;
  CREATE TABLE dbo.#ResultSet (
      DateTime DATETIME,
      GroupID NVARCHAR(50)
  );
  -- #TempB
  IF OBJECT_ID(N'#JoinSet') IS NOT NULL DROP TABLE #JoinSet;
  CREATE TABLE dbo.#JoinSet (
      DateTime DATETIME,
      GroupID NVARCHAR(50)
  );
  
  -- Target Fields - Iterator
  DECLARE field_cursor CURSOR FOR
  SELECT Id, Name, RawTableName
  FROM dbo.[Tag]
  WHERE TagSourceId = @TagSourceID
  AND Name NOT IN ('DateTime','GroupId')
  AND TagTypeId NOT IN (6,7)
  ORDER BY Id;
  
  -- Local Variables
  DECLARE @TagId bigint, @TagName nvarchar(250), @RawTableName nvarchar(250)
  
  OPEN field_cursor;
  
  -- Initial first fetch and store
  FETCH NEXT FROM field_cursor
  INTO @Tag, @TagName, @RawTableName;
  
  -- Verify that the table where the values are stored is not external.
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @RawTableName)
  BEGIN --IF BLOCK
      -- Check @@FETCH_STATUS for any other rows to fetch
      WHILE @@FETCH_STATUS = 0
      -- Loop through each field/column...
      BEGIN --LOOP START
          -- full outer join values from rawtables to ResultSet
  
          -- #TempB = #TempA Join (Select value From dbo...Value Table...)
          DECLARE @field nvarchar(500);
          SET @field = 
          N'SELECT a.*, b.value AS ' + QuoteName(@TagName) +
          N'INTO #JoinSet
            FROM #ResultSet a
            FULL OUTER JOIN ( 
              SELECT
                  CONVERT(datetime2, [DateTime], 1) as DateTime,
                  [GroupId],
                  [Value]
              FROM dbo.' + QuoteName(@RawTableName) +
              N'WHERE TagId = ' + QuoteName(@TagId) + N'AND 
              IsDeleted=0 ) b
            ON a.DateTime = b.DateTime AND a.GroupId = b.GroupId
          ';
          EXEC sp_executesql @field;
  
          -- DROP TABLE #TempA
          IF OBJECT_ID(N'#ResultSet') IS NOT NULL DROP TABLE #ResultSet
          -- #TempA = #TempB
          -- ***** ERROR BLOCK START *****
          -- Returns 'There is already an object named '#ResultSet' in the database.'
          SELECT *
          INTO #ResultSet
          FROM #JoinSet;
          -- ***** ERROR BLOCK END *****
          -- DROP TABLE #TempB
          DROP TABLE #JoinSet;
  
  
          -- Iterate values to next field
          FETCH NEXT FROM field_cursor
          INTO @Tag, @TagName, @RawTableName;
      END --LOOP END
  
  END --IF BLOCK END
  
  CLOSE field_cursor;
  DEALLOCATE field_cursor;
  
  SELECT * FROM #ResultSet;
  DROP TABLE #ResultSet;
  
  GO
  </code></pre>
  
  <p>I have also been looking into creating a recursive procedure/function to handle this, but I'm having trouble using the cursor as a recursive condition, and table variables/temp tables with static column definitions are still an issue.</p>
  
  <p>Any help would be greatly appreciated. If you have questions about the purpose of this script, or any general suggestions, I would very much like to hear it. </p>