SQL回圈未設定其中一個值

這是我在 SSMS 中的 SQL 代碼。它成功地遍歷臨時表并成功洗掉每一行的第一行，但是當它到達帶有 TBL_Crocodile 的行時，它開始混亂并且沒有正確設定變數。

DECLARE
@TagNumber VARCHAR(255) = 'LEC11500',
@BinFrom VARCHAR(255) = 'D5',
@BinTo VARCHAR(255) = 'D6'

BEGIN
CREATE TABLE #ListofTables (
    TableWithBins VARCHAR(255),
    TagColomnName VARCHAR(255),
    BinOrBinNumber VARCHAR(255));

INSERT INTO #ListofTables 
VALUES (
            'TBL_Crocodile',
            'CarcassTagNumber',
            'BinNumber'
        ),
        (
            'TBL_Crocodile',
            'DigitanTagNumber',
            'BinNumber'
        ),
        (
            'TBL_Crocodile',
            'TagNumber',
            'BinNumber'
        );

DECLARE 
 @SQL VARCHAR(500),
 @Count INT,
 @TableWithBins VARCHAR(255),
 @BinOrBinNumber VARCHAR(255),
 @TagNamingConvention VARCHAR(255);



 SET @Count = (SELECT COUNT(TableWithBins) FROM #ListofTables)
 WHILE(@Count > 0)
 BEGIN 

    SET @TableWithBins = (SELECT TOP (1) TableWithBins FROM #ListofTables ORDER BY TableWithBins)
    SET @TagNamingConvention = (SELECT TOP (1) TagColomnName FROM #ListofTables ORDER BY TablewithBins)
    SET @BinOrBinNumber = (SELECT TOP (1) BinOrBinNumber FROM #ListofTables ORDER BY TableWithBins)

    PRINT @TableWithBins
    print @TagNamingConvention
    print @BinOrBinNumber

    SELECT * FROM #ListofTables

    --Set the old binNumber to the new binNumber
    SELECT @SQL = 'UPDATE  '   @TableWithBins   '
                    SET '   @BinOrBinNumber   ' = '''   @BinTo   '''
                    WHERE '   @BinOrBinNumber   ' = '''   @BinFrom   '''
                    AND '   @TagNamingConvention   ' = '''   @TagNumber   '''';
    --PRINT(@SQL);

    DELETE TOP (1) FROM #ListofTables WHERE TableWithBins = @TableWithBins
    SET @TableWithBins =  NULL;
    SET @TagNamingConvention = NULL;
    SET @BinOrBinNumber = NULL;
    SET @Count -= 1
END
    DROP TABLE #ListofTables

END

這是當它到達 TBL_Crocodile 時變數的設定。

(3 rows affected)
TBL_Crocodile
DigitanTagNumber
BinNumber

(3 rows affected)

(1 row affected)
TBL_Crocodile
TagNumber
BinNumber

(2 rows affected)

(1 row affected)
TBL_Crocodile
TagNumber
BinNumber

(1 row affected)

(1 row affected)

這是 tmp 表 #ListofTables，您可以在其中看到我的預期結果。我希望將上面的這三個變數（DigitanTagNumber、TagNumber、TagNumber）設定為下面這個結果中的行（CarcassTagNumber、DigitanTagNumber、TagNumber）。

設定的變數與表中顯示的不匹配，我不知道為什么。

uj5u.com熱心網友回復：

這是使用ID INT IDENTITY列的非游標解決方案。查看代碼中的注釋。

DECLARE
@TagNumber VARCHAR(255) = 'LEC11500',
@BinFrom VARCHAR(255) = 'D5',
@BinTo VARCHAR(255) = 'D6'

BEGIN
CREATE TABLE #ListofTables (
    ID INT IDENTITY, -- Add unique ID column to use as iterator
    TableWithBins VARCHAR(255),
    TagColomnName VARCHAR(255),
    BinOrBinNumber VARCHAR(255));

INSERT INTO #ListofTables( TableWithBins, TagColomnName, BinOrBinNumber )
VALUES (
            'TBL_Crocodile',
            'CarcassTagNumber',
            'BinNumber'
        ),
        (
            'TBL_Crocodile',
            'DigitanTagNumber',
            'BinNumber'
        ),
        (
            'TBL_Crocodile',
            'TagNumber',
            'BinNumber'
        );

DECLARE 
    @SQL VARCHAR(500),
    -- @Count INT, -- No longer needed
    @ID INT,
    @TableWithBins VARCHAR(255),
    @BinOrBinNumber VARCHAR(255),
    @TagNamingConvention VARCHAR(255);


-- Select first record
 SET @ID = (SELECT MIN(ID) FROM #ListofTables)

 -- Loop until @ID becomes NULL
 WHILE( @ID IS NOT NULL )
 BEGIN 
    -- Set variables
    SELECT @TableWithBins = TableWithBins, @TagNamingConvention = TagColomnName, @BinOrBinNumber = BinOrBinNumber FROM #ListofTables WHERE ID = @ID

    PRINT @TableWithBins
    print @TagNamingConvention
    print @BinOrBinNumber

    SELECT * FROM #ListofTables

    --Set the old binNumber to the new binNumber
    SELECT @SQL = 'UPDATE  '   @TableWithBins   '
                    SET '   @BinOrBinNumber   ' = '''   @BinTo   '''
                    WHERE '   @BinOrBinNumber   ' = '''   @BinFrom   '''
                    AND '   @TagNamingConvention   ' = '''   @TagNumber   '''';
    --PRINT(@SQL);

    -- Delete is optional
    --DELETE FROM #ListofTables WHERE ID = @ID
    -- Set next ID. Will be NULL when MAX ID reached
    SET @ID = (SELECT MIN(ID) FROM #ListofTables WHERE ID > @ID )

    SET @TableWithBins =  NULL;
    SET @TagNamingConvention = NULL;
    SET @BinOrBinNumber = NULL;
END

DROP TABLE #ListofTables

END

uj5u.com熱心網友回復：

據我了解您的流程，原則上處理行的順序無關緊要。重要的是每一行都被處理，而且只處理一次。因此，我的建議是使用游標。這將逐行迭代結果集，因此這些條件（即每一行，并且僅一次）會自動滿足，您無需關心順序或從輸入資料中洗掉“正確”行。如果您#ListOfTables從查詢創建您的，您甚至可以擺脫這個臨時表并只迭代查詢結果。

這是一個基于游標的解決方案

-- create your list of tables here
-- ...

DECLARE tablecursor CURSOR FOR
  SELECT tablewithbins, tagcolumname, binorbinnumber
  FROM #ListofTables

DECLARE @SQL VARCHAR(500),
    @TableWithBins VARCHAR(255),
    @BinOrBinNumber VARCHAR(255),
    @TagNamingConvention VARCHAR(255);

OPEN tablecursor

FETCH NEXT FROM tablecursor
INTO @TableWithBins, @TagNamingConvention @BinOrBinNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  
  SELECT @SQL = 'UPDATE  '   @TableWithBins   '
                    SET '   @BinOrBinNumber   ' = '''   @BinTo   '''
                    WHERE '   @BinOrBinNumber   ' = '''   @BinFrom   '''
                    AND '   @TagNamingConvention   ' = '''   @TagNumber   '''';
  -- do whatever you need to do in the loop with the variables
  -- no need to delete anything from the #listoftables
  
  FETCH NEXT FROM tablecursor
  INTO @TableWithBins, @TagNamingConvention @BinOrBinNumber
END

CLOSE tablecursor
DEALLOCATE tablecursor

根據您必須處理的行數，這可能還會獲得一些性能，因為您不需要在回圈的每次迭代中執行查詢來處理下一行。這一切都由作為游標源的頂部的單個查詢處理。

標籤：sql sql-server 变量 while 循环 临时表

上一篇：如何創建兩個變數列

下一篇：除錯模式與正常運行的不同排序結果（SortedSet）