时间戳

首页 > 解决方案 > SQL - 用+=连接变量while循环中的字符串

sql - SQL - 用+=连接变量while循环中的字符串

问题描述

在下面的这个例子中我不能连接！当我循环时，我得到了 2 个正确的结果。当我连接 @MaterialCompositionEn += ', ' 它工作正常当我尝试连接 @MaterialCompositionEn += 相同的查询以获得第二行时，我有一个空值！

    DECLARE @MaterialCompositionId int = 475;
    DECLARE @MaterialCompositionKey nvarchar(50) = '202071512324138';
    DECLARE @Records nvarchar(250);
    DECLARE @RecordProceed int;
    DECLARE @MaterialCompositionEn nvarchar(500);

    SET @Records =  (SELECT STRING_AGG(Id, ',') FROM MaterialCompositions mc WHERE mc.MaterialCompositionId = @MaterialCompositionId)

    WHILE len(@Records) > 0
    BEGIN

        SET @RecordProceed = CAST(LEFT(@Records,4) AS int)
        if @RecordProceed > 0
            BEGIN
                SET @Records = REPLACE(@Records,substring(@Records, 1, 4),'')
            END
        if len(@Records) > 4
            BEGIN
                SET @Records = REPLACE(@Records,substring(@Records, 1, 1),'')
            END

        if len(@MaterialCompositionEn) > 0
            BEGIN
                SET @MaterialCompositionEn += ', '
            END
PRINT 'MaterialCompositionEn1: ' +  @MaterialCompositionEn

        SET @MaterialCompositionEn =
            (SELECT COALESCE (CAST(MaterialProportion AS nvarchar) + '% ', '') +
                (SELECT mp.MaterialPrimaryEn + 
                    COALESCE(
                        (SELECT ' (' + ms.MaterialSecondaryEn + ')' AS MS1 FROM dbo.MaterialSecondaries AS ms WHERE ms.Id = mc.MaterialSecondaryId)
                    , '')
                FROM dbo.MaterialPrimaries AS mp WHERE mp.Id = mc.MaterialPrimaryId)
            FROM MaterialCompositions mc WHERE mc.Id = @RecordProceed
            )

PRINT 'MaterialCompositionEn2: ' +  @MaterialCompositionEn
    END

结果：

MaterialCompositionEn2: 20% Cashmere
MaterialCompositionEn1: 20% Cashmere, 
MaterialCompositionEn2: 80% Wool

现在，当我更改为：

SET @MaterialCompositionEn +=
            (SELECT COALESCE......

我期待 20% 的羊绒，80% 的羊毛，而不是我的 3 个打印件是 NULL 我尝试铸造但无济于事。

任何想法？提前致谢

标签： sqlconcatenation

解决方案

我猜有一种更简单的方法可以做你想做的事。但是，我认为问题在于您需要初始化字符串。所以在代码块的顶部放置：

SET @MaterialCompositionEn = '';

推荐阅读