sql-server - 如何从数组中提取变量以便在我的查询参数中使用这些变量？我在 SQL Server Management Studio 中使用 T-SQL

Question

我正在为我的工作创建一个查询，涉及运行一个查询，然后在通过我们系统的数据库的迭代中使用该查询的结果。我试图将其分解为一些需要的组件。

1. 运行将获取要迭代的数据集的查询
2. 开始一个循环，该循环将为第一个查询的每个结果运行 1 次。
3. 每次循环运行时，它都会对数据库进行查询。每次从第一个数据集中获取的变量都会更改数据库的名称，结果将全部附加到单个表或结果中。
4. 随着循环的每次迭代，将一个变量更改为迭代结果中每个项目的 ID。

我对在 SQL 中设置循环或变量不是很熟悉，并且无法理解它。

这是在 SQL Server Management Studio 中运行的。

这就是我到目前为止所发现的：代码开始创建一个 select 语句，以便找到我需要遍历的所有数据库名称。有两个状态代码，当它处于该状态时，将遍历表的项目编号和大小。

第二个 select 语句获取所有需要转换的信息并将其放入表中。循环将在此查询之前开始，然后多次运行，使用第一个表中每个结果的 id 作为要查询的数据库的名称。

/* 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 > 0 
    AND Overwrite = 'Append Only' 
    AND endtimeutc > '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

现在上面的代码工作正常，但我需要弄清楚如何将迭代放在中间语句周围并创建然后将变量设置为随着每次迭代而更改为第一个表中正在迭代的条目的 id。

感谢您提供的任何帮助。

标签： sql-servertsqlvariablesiteration

Answer 1

我不知道您的数据库的结构或列名（因此我刚刚猜到了数据库名称列），但这是我将如何构建循环机制的方式。下面的查询示例仅填充数据库引擎名称，但可以扩展以填充数据库和表名称。

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