• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1268
  • Last Modified:

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
0
softdimensions
Asked:
softdimensions
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#_Toc205129485

This should solve your problem
0
 
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

0
 
softdimensionsAuthor Commented:
This method is clean, clean most of all it works,  I prefer the first option although they both accomplish the task
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now