CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
shmnff
shmnff

Reputation: 739

TSQL : Can't concatenate strings in while loop

Trying to concatenate strings with code

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;

So, in @urlList I get only one value.

But printing

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

returns all values as well.

Can somebody explain how to fix script for getting all values?

Here's table declaration:

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

Followed on stackoverflow's rules I'm adding this text to my question, because it says that is question contains mostly code.

Upvotes: 2

Views: 2202

Answers (3)

shmnff
shmnff

Reputation: 739

Problem was in data type [FileName][nchar](300) NULL, - just need to change to nvarchar. Thanks everyone for brilliant solutions, I'll certainly use them.

Upvotes: 1

Alan Burstein
Alan Burstein

Reputation: 7918

Here's four different approaches using the following sample data:

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

Option 1: variable concatination

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

Option 2: for xml path('') No XML Protection

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,'')

Option 3: for xml path('') With XML Protection

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

Option 4: STRING_AGG (SQL Server 2017 only)

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

Upvotes: 5

Zohar Peled
Zohar Peled

Reputation: 82474

The recommended way to mimic group concat is to use for xml and stuff:

SELECT @urlList = STUFF(
    (SELECT '|' + [FileName] 
     FROM [stgInfo]
     FOR XML PATH(''))
, 1, 1, '')

The inner select concatenates the separator with the column value, and the stuff removes the first separator.

Upvotes: 3

Related Questions