sql-server - 在迭代循环中连接字符串: += does not work as expected

Question

我有必须从其值填充的本地表和字符串:

    DECLARE @#SomeTable  TABLE ( some columns ..)
    DECLARE @SomeString  varchar(8000) = 'init string'

迭代时

    WHILE EXISTS(SELECT * FROM  @#SomeTable)
     BEGIN
        // [somecolumn] is declared temp variable
        SELECT TOP 1 @somecolumn = somecolumn FROM  @#SomeTable

        PRINT 'before ' + @SomeString // 'init string'
        PRINT [some values from SomeTable]  // this OK
        SET @SomeString += [some values from SomeTable] 
        PRINT 'after ' +  @SomeString //'init string' UPDATE NOT TAKE PLACE!!!!

        DELETE  @#SomeTable Where somecolumn = @somecolumn
     END

我发现串联失败。为什么？

编辑:

这是一段原始代码:

    /*Represents [WHERE] clause for retrieving values from specifyed range*/
DECLARE @WHEREclause nchar(1000) = 'WHERE '
/*Represents [ORDER BY] clause for sorting in right order and direction {ASC|DESC}*/
DECLARE @ORDERBYclause nchar(1000) = 'ORDER BY '
/*Dynamic query that returns end result*/
DECLARE @sqlCmd varchar(8000) = 
'SELECT 
        img,
        capacity,
        price,
        Id
 FROM HDD  '
/* -a- filling table for input values*/
INSERT INTO @#SequenceTable(columnName,columnValue,comparator,isASC,columnOrder)
SELECT 
    columnName,
    columnValue,
    comparator,
    isASC,
    ROW_NUMBER() OVER (ORDER BY  outOrder) AS columnOrder
FROM
(
        SELECT 'buffer' as columnName, CAST(@buffer AS nchar(20)) as columnValue, @bufferCmp as comparator, @bufferASC as isASC, @bufferOrder as outOrder
    UNION  
        SELECT 'capacity', CAST(@capacity AS nchar(20)), @capacityCmp, @capacityASC, @capacityOrder  
    UNION  
        SELECT 'price', STR(@price,20,2),  @priceCmp, @priceASC, @priceOrder
    UNION  
        SELECT 'angle_speed', CAST(@angleSpeed AS nchar(20)), @angleSpeedCmp ,@angleSpeedASC,@angleSpeedOrder
) AS AnyName
ORDER BY columnOrder 

/*---/a-----------------------------------------------------------------------------------*/

/*variables for above fields*/
DECLARE @columnName nchar(20)
DECLARE @comparator char
DECLARE @columnValue nchar(20)
DECLARE @isASC char

WHILE EXISTS(SELECT * FROM  @#SequenceTable)
BEGIN

    SELECT TOP 1 @columnName = columnName FROM  @#SequenceTable
    SELECT TOP 1 @comparator = comparator FROM  @#SequenceTable 
    SELECT TOP 1 @columnValue = columnValue FROM  @#SequenceTable 
    SELECT TOP 1 @isASC = isASC FROM  @#SequenceTable 

    IF @WHEREclause != 'WHERE '
        BEGIN
            SET @WHEREclause += ' AND '
        END
    PRINT 'before ' + @WHEREclause
    PRINT CONCAT(RTRIM(@columnName), @comparator, @columnValue)
    SET @WHEREclause += CONCAT(RTRIM(@columnName), @comparator, @columnValue)
    PRINT 'after ' +  @WHEREclause

    IF @ORDERBYclause != 'ORDER BY '
        BEGIN
            SET @ORDERBYclause += ','
        END
    IF @isASC = '1'
       SET @ORDERBYclause += CONCAT(RTRIM(@columnName),' ASC ')
    ELSE
       SET @ORDERBYclause += CONCAT(RTRIM(@columnName),' DESC ')

    Delete  @#SequenceTable Where columnName = @columnName

END

这是打印结果:

        before WHERE 
        angle_speed=7400 
        after WHERE 

+= 无法按预期工作。如果我使用

@SomeString = 'some value' 

那么更新是可见的，但如果我改为使用

@SomeString += 'some value' 

更新不可见

Answer 1

我可以向您保证 SQL Server 中没有错误，并且 += 完全按照预期工作。我尝试了以下代码:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000);

WHILE EXISTS (SELECT * FROM  @#SomeTable)
BEGIN
    SELECT TOP 1 @somecolumn = somecolumn FROM @#SomeTable;

    SET @SomeString += @somecolumn;

    PRINT @SomeString; -- Works fine!!!

    DELETE  @#SomeTable Where somecolumn = @somecolumn;
END

这是我的结果:

init stringa
init stringabbb
init stringabbbccccc

由于不可能准确地告诉你在代码中做了什么(你已经混淆了最重要的部分)，也许你可以从那里开始？当然，要么表中存在 NULL 值，要么分配不正确，要么分配给错误的变量。同样，无法分辨，因为您隐藏了代码的关键部分!

此外，由于您似乎并不关心顺序，因此您也可以在不循环的情况下执行此操作:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000);

SELECT @SomeString += somecolumn FROM @#SomeTable;

PRINT @SomeString;

结果:

init stringabbbccccc

如果您关心顺序，您仍然可以在不循环的情况下执行此操作 - 使用 XML 技巧按该顺序连接，然后将其附加到初始化字符串:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000) = '';

SELECT @somecolumn = (SELECT '' + somecolumn FROM @#SomeTable
ORDER BY somecolumn DESC
FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)');

PRINT @SomeString + @somecolumn;

结果:

init stringcccccbbba

在更现代的版本(SQL Server 2017+)上，您可以执行以下操作:

DECLARE @#SomeTable TABLE (somecolumn varchar(8000));

INSERT @#SomeTable VALUES('a'), ('bbb'), ('ccccc');

DECLARE @SomeString  varchar(8000) = 'init string',
        @somecolumn  varchar(8000);

SELECT @somecolumn = STRING_AGG(somecolumn, '')
  WITHIN GROUP (ORDER BY somecolumn DESC)
  FROM @#SomeTable;

PRINT @SomeString + @somecolumn;