Assign multiple values to a variable SQL Server 2005

Posted on 2009-02-14
Last Modified: 2012-05-06
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
Question by:softdimensions
    LVL 57

    Expert Comment

    by:Raja Jegan R
    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
    LVL 51

    Accepted Solution

    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


    Author Closing Comment

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now