Как извлечь переменные из массива, чтобы использовать их в параметрах моего запроса? Я использую T-SQL в SQL Server Management Studio

Я не знаю структуру вашей базы данных или имена столбцов (поэтому я только что догадался о столбце имени базы данных), но вот как я бы структурировал механизм цикла. Приведенный ниже пример запроса заполняет только имя ядра базы данных, однако его можно расширить, чтобы заполнить имена базы данных и таблиц.

/*check if the #databases table is already present and then drop it*/
IF OBJECT_ID('tempdb..#databases', 'U') IS NOT NULL
begin
    drop table #databases;
end

 select 
                * into #databases
                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]*/

declare @runs int = 1; /*this will track the number of times you iterate over the result set*/
declare @max int = 0; /*this will be the limit*/
declare @sql nvarchar(max)=''; /*this will allow you to dynamically populate the sql*/
declare @databasename sysname='' /*this will allow you to populate each database name*/

select @max=count(*) from #databases;
 /*the limit is now the number of databases inserted in to this table*/

/*check if your temp table exists and drop if neccessary*/
IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL
begin
    drop table #temptable;
end
/*create the temp table as you won't be able to use select into on each loop as your table will already exist on the second loop*/
create table #temptable 
(
    DestinationWorkSpace nvarchar(150),
    TotalItem nvarchar(30),
    Filesize int,
    FileNumber money
);

while @runs<=@max
begin



            /*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 statment should be inside the loop. One time 
for each DB that appeared in the first query's results.*/
/*begin the loop by assigning your dsatabase name, I don't know what the column is called so I have just called it databasename for now*/
select top 1 @databasename = databasename from #databases;
/*generate your sql using the @databasename variable, if you want to make the database and table names dynamic too then you can use the same formula*/
select @sql=N' insert #temptable (DestinationWorkspace, TotalItems, FileSize, FileNumber)
            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 FROM ['+cast(@databasename as nvarchar(128))+'].[EDDSDBO].[JobHistory]
              where ItemsTransferred > 0 and Overwrite = ''Append Only'' and endtimeutc > ''2019-03-31''';
/*execute the sql*/
    exec (@sql);
/*remove that row from the databases table so you don't re-do that row*/
delete from #databases where databasename=@databasename;

            /* The 'from' portion of the statement is where I'll be needing to put in the artifiactID 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*/


/*end the loop*/
select @runs=@runs+1;
end

            /* 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