1

I am trying to coalesce a large number of record Ids into a single, comma-separated string variable. However, the resulting n/varchar variable is always truncated at 4096 characters.

Here is the SQL statement I'm attempting -

DECLARE @docIds varchar(max)
SELECT @docIds = COALESCE(@docIds + ',', '') + CAST(docUid AS varchar(32))
FROM Documents

I have found a number of other SO links regarding n/varchar(max) truncation and concatenation, but cannot seem to resolve my query above. I've also tried -

DECLARE @docIds varchar(max) SET @docIds = ''
SELECT @docIds = @docIds + ',' + CAST(docUid AS varchar(32))
FROM Documents
SET @docIds = STUFF(@docIds, 1, 1, '');

From both queries above, SELECT LEN(@docIds) will only ever return 4096 max.

QUESTION: How can I accomplish the above retrieval into a single string variable without truncation issues?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mjmoody383
  • 358
  • 6
  • 19
  • 3
    Also note nvarchar != varchar. – Joe Jul 04 '13 at 04:34
  • 1
    I have try your query for some tables in my databases and it seems to be working. I guess the problem is that you are checking for NULL values the variable "@docIds". You should check the "docUid" column for NULL value because cast of NULL is again NULL, and concatenation of string and NULL is NULL. – gotqn Jul 04 '13 at 06:21

1 Answers1

1

Try this one -

DECLARE @docIds VARCHAR(MAX)

SELECT @docIds = STUFF((
    SELECT ',' + CONVERT(VARCHAR(50), docUid)
    FROM dbo.Document
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')

SELECT @docIds, LEN(@docIds)
Devart
  • 119,203
  • 23
  • 166
  • 186