sql-server - TSQL : Can't concatenate strings in while loop

Question

尝试用代码连接字符串

DECLARE @separator [nchar](1) = '|',
         @urlList [nvarchar](4000) = '',
         @urlTemp [nvarchar](4000);

DECLARE UrlCursor CURSOR FOR
SELECT [FileName] FROM [stgInfo];

OPEN UrlCursor;
FETCH NEXT FROM UrlCursor INTO @urlTemp;
SET @urlList += @urlTemp;

WHILE @@FETCH_STATUS = 0
   BEGIN
         SET @urlList += @separator + @urlTemp;
         FETCH NEXT FROM UrlCursor INTO @urlTemp;
   END
CLOSE UrlCursor;
DEALLOCATE UrlCursor;

因此，在 @urlList 中我只得到一个值。

但是打印

   BEGIN
         print @urlTemp;
         FETCH NEXT FROM UrlCursor INTO @urlTemp;
   END

也返回所有值。

有人可以解释如何修复脚本以获取所有值吗？

这是表格声明:

SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [stgInfo]
(
    [Guid]      [uniqueidentifier] NOT NULL,
    [Number]    [nvarchar](100)    NULL,
    [OrderDate] [nvarchar](20)     NULL,
    [JrnType]   [nchar](10)        NULL,
    [Status]    [nchar](20)        NULL,
    [FileName]  [nchar](300)       NULL,

    CONSTRAINT   [PK_stgInfo] PRIMARY KEY CLUSTERED 
    (
         [Guid] ASC
    )
    WITH
    (
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         IGNORE_DUP_KEY = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON
    )
    ON [PRIMARY]
)
ON [PRIMARY];
GO

遵循 stackoverflow 的规则，我将此文本添加到我的问题中，因为它说问题主要包含代码。

Answer 1

以下是使用以下示例数据的四种不同方法:

-- using table variable for sample data
declare @stginfo table ([filename] varchar(4000));
insert @stginfo values ('abc.txt'),('zzz.sql'),('hellokitty.bat');

选项 1:可变连接

DECLARE @separator [nchar](1) = N'|',
         @urlList [nvarchar](4000) = N'',
         @urlTemp [nvarchar](4000);

SELECT @urlList += [FileName]+@separator
FROM @stgInfo; -- Order by is not guaranteed with this method

SELECT urlList = substring(@urlList,1,len(@urlList)-1);

选项 2:对于 xml 路径 ('') 无 XML 保护

SELECT urlList = 
STUFF((
  SELECT [filename] + @separator
  FROM @stginfo
  ORDER BY [filename] -- only required if you must get this text in a specific order
  FOR XML PATH('')),1,1,'')

选项 3:对于带有 XML 保护的 xml 路径('')

SELECT urlList = 
STUFF((
  SELECT [filename] + @separator
  FROM @stginfo
  FOR XML PATH(''), TYPE).value('(text())[1]', 'nvarchar(4000)'),1,1,'')

选项 4:STRING_AGG(仅限 SQL Server 2017)

SELECT urlList = STRING_AGG([filename],'|') WITHIN GROUP (ORDER BY [Filename])
FROM @stginfo;