Is it possible to create a variable which store unlimited character in SQLserver 2005?

Question

I wrote some T-SQL which will concatenate all my strings in one column together but nvarchar(max) is not enough to store it.

Is there another way?

The code I'm trying to use is:

DECLARE @codes NVARCHAR(max) 
SET @codes = '' 
SELECT @codes = @codes + ',' + CONVERT(nvarchar,code) 
FROM dbo.listing SELECT @codes

Answer 1

Unlimited, no. At some point you're going to run out of storage space. Seriously, if you find yourself creating individual elements that wont fit within about 2³¹ bytes, you're doing something wrong.

Why don't you tell us the "real" problem that you're trying to solve? Without the preconceived notions that you must do it in a certain way.

Answer 2

The only suggestion that I can think off the top of my head would be to use varchar(max) if you aren't worried about international characters. But that isn't going to give you a massive increase in storage capacity.

That must be a really massive SQL string though for it not to fit.

Answer 3

You need to cast the code column to nvarchar(max) or else it will truncate:

DECLARE @codes NVARCHAR(max) 
SET @codes = '' 
SELECT @codes = @codes + ',' + CONVERT(nvarchar**(max)**,code) 
FROM dbo.listing SELECT @codes

Answer 4

What are you planning to do with this awful variable? Why not just write a join to the table instead?