如何将查询中的值输入到声明语句中，以便在查询中循环

Question

我正试图从一个表中创建一个不同的列表，然后从结果中循环遍历每一行。我需要捕获每一行的值，将其输入到声明语句中，然后运行另一个查询。查询完成后，它从不同列表中获取一个新值，并重复，直到所有值都循环通过为止。

以下是我目前所拥有的：

''

Declare @FDIC_CERT int
Declare MY_CUR CURSOR
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
Select Distinct FDIC_Cert 
from bank.SOD_BranchList
OPEN MY_CUR
FETCH NEXT FROM MY_CUR INTO @FDIC_CERT
WHILE @@FETCH_STATUS = 0
    Begin

    DECLARE @CERT INT 
    Set @CERT = @FDIC_CERT
    DECLARE @MyList TABLE (Value int)
    INSERT INTO @MyList SELECT DISTINCT UniqueID from bank.SOD_BranchList WHERE FDIC_CERT = @CERT
    DECLARE @COUNTER INT = 0;
    DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
    DECLARE @VALUE INT;
    WHILE @COUNTER < @MAX
    BEGIN
    SET @VALUE = (SELECT VALUE FROM
        (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index], VALUE FROM @MyList) R
        ORDER BY R.[index] OFFSET @COUNTER
        ROWS FETCH NEXT 1 ROWS ONLY);
    PRINT @VALUE
    --delete from bank.DFC_SOD_Data
    insert into bank.Master_Competitor_List
    select 
    CAST(@Value AS int) ComparisonBranch,
    CAST(@CERT AS int) ComparisonBankID,
    a.BranchType ComparisonBranchType,
    a.Year,
    c.Distance,
    a.UniqueID ToBranch,
    CASE WHEN a.UniqueID=@Value THEN 'This Branch' 
    --WHEN a.FDIC_CERT IN (4666, 49600) THEN 'Other DFC Branches'
    ELSE 'Competitor' END Type,
    a.BranchName Branch,
    a.BranchType BranchType,
    a.FDIC_CERT BankID,
    a.BankName,
    a.Latitude,
    a.Longitude,
    a.Deposits
    --into bank.DFC_SOD_Data
    from bank.SOD_BranchList a
    inner join (
        select UniqueID_From, Year, Distance from bank.BranchDistanceMatrix_Copy WHERE ComparedTo = CAST(@VALUE as int) 
    ) c on a.UniqueID = c.UniqueID_From and 2021 = c.[YEAR]
    WHERE cast(c.Distance as float) <= 5
    SET @COUNTER = @COUNTER + 1
    PRINT @FDIC_CERT
    FETCH NEXT FROM MY_CUR INTO @FDIC_CERT
    END
    CLOSE MY_CUR
    DEALLOCATE MY_CUR
END

''

我知道我应该避免使用光标，所以任何其他建议都将不胜感激。我目前得到的错误是"；名称为"MY_CUR"的光标不存在"；

Answer 1

在不需要的时候不要使用游标。但如果要在行上循环，请使用游标，而不是在单行上使用其他循环结构。如果要使用逐行处理来写入this，则需要两个游标：外部游标和替换WHILE@COUNTER<MAX循环。

使用嵌套的光标看起来像这样：

Declare @FDIC_CERT int
Declare CERT_CUR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
Select Distinct FDIC_Cert from bank.SOD_BranchList
OPEN CERT_CUR
FETCH NEXT FROM CERT_CUR INTO @FDIC_CERT
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @BRANCH_ID INT
    DECLARE BRANCH_CUR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR 
    SELECT DISTINCT UniqueID from bank.SOD_BranchList WHERE FDIC_CERT = @FDIC_CERT
    OPEN BRANCH_CUR
    FETCH NEXT FROM BRANCH_CUR INTO @BRANCH_ID
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @BRANCH_ID
        --delete from bank.DFC_SOD_Data
        insert into bank.Master_Competitor_List
        select 
        CAST(@BRANCH_ID AS int) ComparisonBranch,
        CAST(@FDIC_CERT AS int) ComparisonBankID,
        a.BranchType ComparisonBranchType,
        a.Year,
        c.Distance,
        a.UniqueID ToBranch,
        CASE WHEN a.UniqueID=@BRANCH_ID THEN 'This Branch' 
        --WHEN a.FDIC_CERT IN (4666, 49600) THEN 'Other DFC Branches'
        ELSE 'Competitor' END Type,
        a.BranchName Branch,
        a.BranchType BranchType,
        a.FDIC_CERT BankID,
        a.BankName,
        a.Latitude,
        a.Longitude,
        a.Deposits
        --into bank.DFC_SOD_Data
        from bank.SOD_BranchList a
        inner join (
            select UniqueID_From, Year, Distance from bank.BranchDistanceMatrix_Copy WHERE ComparedTo = CAST(@BRANCH_ID as int) 
        ) c on a.UniqueID = c.UniqueID_From and 2021 = c.[YEAR]
        WHERE cast(c.Distance as float) <= 5
        FETCH NEXT FROM BRANCH_CUR INTO @BRANCH_ID
    END
    CLOSE BRANCH_CUR
    DEALLOCATE BRANCH_CUR
    FETCH NEXT FROM CERT_CUR INTO @FDIC_CERT
END
CLOSE CERT_CUR
DEALLOCATE CERT_CUR

但是，由于您没有为每一行调用一些现有的存储过程，因此在单个查询中应该可以直接执行这一操作，比如：

    with b as
    (
       SELECT DISTINCT FDIC_CERT, UniqueID BRANCH_ID
       from bank.SOD_BranchList c
       join bank.SOD_BranchList b
         on c.FDIC_CERT = b.FDIC_CERT
    )
    insert into bank.Master_Competitor_List
    select 
    CAST(b.BRANCH_ID AS int) ComparisonBranch,
    CAST(b.FDIC_CERT AS int) ComparisonBankID,
    a.BranchType ComparisonBranchType,
    a.Year,
    c.Distance,
    a.UniqueID ToBranch,
    CASE WHEN a.UniqueID=b.BRANCH_ID THEN 'This Branch' 
    --WHEN a.FDIC_CERT IN (4666, 49600) THEN 'Other DFC Branches'
    ELSE 'Competitor' END Type,
    a.BranchName Branch,
    a.BranchType BranchType,
    a.FDIC_CERT BankID,
    a.BankName,
    a.Latitude,
    a.Longitude,
    a.Deposits
    --into bank.DFC_SOD_Data
    from bank.SOD_BranchList a
    inner join (
        select UniqueID_From, Year, Distance from bank.BranchDistanceMatrix_Copy WHERE ComparedTo = CAST(b.BRANCH_ID as int) 
    ) c on a.UniqueID = c.UniqueID_From and 2021 = c.[YEAR]
    WHERE cast(c.Distance as float) <= 5