title: How do I pull variables from an array in order to use those to within my query parameters? I'm using T-SQL in SQL Server Management Studio
body: <p>I'm creating a query for my work involving running a query then using the results of that query within an iteration through the dbs of our system. I'm trying to beak this down into a few needed components.</p>
  
  <ol>
  <li>Run query that will get the dataset to be iterated through</li>
  <li>Begin an loop that will run 1 time for each result from the first query.</li>
  <li>Each time the loop runs it will make a query into a database. The name of the DB will be changed each time by a variable taken from the first dataset and the results will all be appended into a single table or result. </li>
  <li>With each iteration of the loop change a variable into the ID of each item within the results being iterated through.</li>
  </ol>
  
  <p>I'm not very familiar with setting up loops or variables in SQL and am having trouble understanding it. </p>
  
  <p>This is running in SQL Server Management Studio.</p>
  
  <p>This is what I've figured out so far: the code starts off making a select statement in order to find all the names of the databases that I'll need to iterate through. There are two status codes that when it is that status the table is to be iterated through for its item numbers and its size. </p>
  
  <p>The second select statement grabs all the information that will need to be converted and puts it into a table. The loop would begin before this query then run it multiple times, using the ids of each result from the first table as the name of the db to be queried. </p>
  
  <pre><code>/* This is the code I have so far. I'll explain what I'm doing and thinking in the comments*/
  
  /* In this first select statement I'm finding all the databases that 
     will need to be iterated through. I figure I can pull the names, 
     create a table or array that contains them then run the iteration 
     once per item in the array using the DB name as a variable that will 
     change with each iteration */
  
  select *
  from edds.eddsdbo.[Case]
  where statuscodeartifactid like 178079 
     or StatusCodeArtifactID like 103428
  
  /* Once this first statement has been run I will now I have a 
     number column that is the artificatID. Each database has an area that is 
     titled [EDDS'artifactID']. So if the artifactID = 1111111,
     then the DB would be accessed at [EDDS1111111] */
  
  Drop Table #temptable
  
  /* If the temptable has been created before this drops the table 
     so the operation can be run again and refill the table.*/
  
  /* This select statement pulls the information that will be placed 
     into the temptable. This second statement should be inside the loop. 
     One time for each DB that appeared in the first query's results. */
  
  SELECT 
      [DestinationWorkspace],
      [TotalItems], [FilesSize],
      CAST(LEFT(FilesSize, PATINDEX('%[0-9-.][^0-9-.]%', FilesSize )) AS MONEY) AS Filenumber,
      LTRIM(RIGHT(FilesSize, LEN(FilesSize) - PATINDEX('%[a - z]%', FilesSize ))) AS Unit
  INTO
      #temptable
  /* The 'from' portion of the statement is where I'll be needing to put 
     in the artifactID variable. Where it says [EDDS1111111] I'd like to 
     be able to have to say [EDDS@variable] where that variable is 
     whatever the artifactID is of that particular round of iteration*/
  FROM 
      [EDDS1111111].[EDDSDBO].[JobHistory]
  WHERE
      ItemsTransferred &gt; 0 
      AND Overwrite = 'Append Only' 
      AND endtimeutc &gt; '2019-03-31'
  
  /* After the above statement has been run multiple times and 
     appended all queries into the temp table the final select statement 
     does some math on the entire table and converts the values into the 
     desired units.*/
  
  select 
      DestinationWorkspace, TotalItems,
      case
         when right([Unit], 2) = 'KB'
            then FileNumber / 1024 / 1024
         when right([Unit], 2) = 'MB'
            then FileNumber / 1024
         else FileNumber
      end as 'Gigs'
  from 
      #temptable
  </code></pre>
  
  <p>Right now the above code works fine but I need to figure out how to put the iteration around the middle statement and create then set the variable to change with each iteration to be the id of the entry that is being iterated over in the first table. </p>
  
  <p>Thank you for any help you can offer.</p>