CODEWITHSUNDEEP
t-sql
pang
pang

Reputation: 4174

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

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

Upvotes: 0

Views: 1118

Answers (4)

HLGEM
HLGEM

Reputation: 96600

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

Upvotes: 0

Adam Ruth
Adam Ruth

Reputation: 3655

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

Upvotes: 0

Marjon
Marjon

Reputation:

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.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 882028

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 231 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.

Upvotes: 3

Related Questions