SQL loop not setting one of the values-CodePudding

This is my SQL code in SSMS. It loops through the temporary table successfully and deletes the top row of every row successfully, but when it gets to the row with TBL_Crocodile, it starts to mess up and doesn't set the variables correctly.

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

This is what the variables are set to when it gets to the 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)

And this is the tmp table #ListofTables where you can see my expected result. I would like those three variables above (DigitanTagNumber, TagNumber, TagNumber) to be set the rows in this result below (CarcassTagNumber, DigitanTagNumber, TagNumber).

The set variable don't match up with what shows in the table and i'm not sure why.

CodePudding user response：

This is a non-cursor solution using an ID INT IDENTITYcolumn. See comments in code.

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

CodePudding user response：

As far as I understand your process, the order in which the rows are handled in principle doesn't matter. It's just important that every row is handled, and it's handled only once. Thus my suggestion with a cursor. This will iterate the result set row by row, so these conditions (ie every row, and only once) are automatically fulfilled and you don't need to care about order or deleting the "correct" row from your input data. If you create your #ListOfTables from a query, you can even get rid of this temporary table and just iterate over the query result.

Here is a cursor based solution


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

Depending on the number of rows you have to handle, this will probably also gain some performance, because you don't need to execute a query in every iteration of the loop to get the next row to handle. This is all handled by the single query on top which acts as source for the cursor.