Assign multiple values to a variable SQL Server 2005

I would like to do the following without using cursors.  Is it possible?

If there was 1 row I would do the following
Select @variable = Field_Name from Table Where ID = 5

But what is the best way to accomplish the following
(syntax obviously won't work but it demonstrates the issue)

Select @variable = Field_Name from Table Where ID IN (5,6,7)

The goal is to have @variable = Value1, Value2, Value3 so I can use the concatenated string later in the procedure
Who is Participating?
Mark WillsTopic AdvisorCommented:
OK, let's try this then....  two possible methods...
-- first we create a temp table just so we can demonstrate the choices
create table #table (id int, fieldvalue varchar(50))
- then populate that test table with some sample data
insert #table values (1,'ID-1')
insert #table values (2,'ID-2')
insert #table values (3,'ID-3')
insert #table values (4,'ID-4')
insert #table values (5,'ID-5')
insert #table values (6,'ID-6')
insert #table values (7,'ID-7')
-- method 1 using a select variable
declare @var1 varchar(200)
select @var1 = isnull(@var1,'') +  ','+convert(varchar,fieldvalue) from #table where id in (3,4,5)
-- show the results
select substring(@var1,2,200)    -- get rid of first comma
-- method 2 using an XML trick
declare @var2 varchar(200)
set @var2 = substring((select ','+convert(varchar,fieldvalue) from #table where id in (3,4,5) for xml path('')),2,200)
-- show the results
select @var2

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Its not possible both theoretically and practically.

either you can use a CTE function or any as described in the link below to save you comma separated value into a variable:

Check out this one "Concatenating values when the number of items is not known " in the link

This should solve your problem
softdimensionsAuthor Commented:
This method is clean, clean most of all it works,  I prefer the first option although they both accomplish the task
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.