Alternative to a cusor ?

[jendeacott]

Hi,

I need to store one field from every row of a result set in a single variable.

EG Resultset,

1 Jo
2 Frank
3 Sid

@variable = "Jo Frank Sid"

I know I could do this using a cursor but understand this is a bad option.

Thanks in advance,
John

Find out you can improve your plant security and plant tracking
¦

 

[JamesLean]

Try this:

--dummy data
DECLARE @t table (name varchar(10))

INSERT @t VALUES ('Jo')
INSERT @t VALUES ('Frank')
INSERT @t VALUES ('Sid')

--real stuff starts here
DECLARE @var varchar(50)

SELECT @var = ISNULL(@var + ' ' + name, name)
FROM @t

PRINT 'var = "' + @var + '"'

--James

 

[jendeacott]

Cheers James that works perfectly.

Out of interest waht is the ISNULL doing, with out sounding too stupid?

Thanks Again

Find out you can improve your plant security and plant tracking
¦

 

[JamesLean]

When you start, @var is null. This means on the first concatenation "@var + ' ' + name" will always be null too so it just sets @var = to the first name (Jo).

On subsequent concatenations, it just adds "' ' + name" to @var.

Basically it just means you don't have to worry about chopping off leading/trailing spaces after you've done the concatenation.

--James

 

[vongrunt]

One problem: replace middle entry( Frank ) with NULL and try again

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[JamesLean]

Yes, I did think of that while I was writing my explanation but forgot to add it!

WHERE name IS NOT NULL

--James