CODEWITHSUNDEEP
sql-server
Vivekh
Vivekh

Reputation: 4259

Assign single variable a multiple rows using Select query in stored procedure

My query is to assign single variable a multiple rows using Select query in stored procedure For example: I get 10(say 1 to 10) employee ids from from Employee table declare @id int

select @id =EmpId from Employee

select @id

This will return 10 rows(i.e.Employee id , suppose there are 10 rows in a table) now my question is how i will get this all rows in a single variable and use that employee id one by one to perform some calculation.

Upvotes: 8

Views: 43607

Answers (4)

David Rushton
David Rushton

Reputation: 5030

You can squeeze each of the rows into one variable, but as you want to process the output one by one I would recommend avoiding this approach:

/*
 * Remember that NULL + any string = NULL.
 * Provide the concat var with a default value.
 */
DECLARE @ConCat         VARCHAR(255) = '';  
DECLARE @SampleTable    TABLE
    (
        Value   VARCHAR(5)
    )
;

-- Populate the sample table.
INSERT INTO @SampleTable (Value)
    VALUES
        ('a'),
        ('b'),
        ('c')
;

-- Concatenate the values into one string.
SELECT
    @ConCat = @ConCat + Value
FROM
    @SampleTable
;


-- Return the concatenated string
SELECT
    @ConCat
;

Upvotes: 2

RollingBoy
RollingBoy

Reputation: 2817

If processing rows one by one is necessary, cursor will be useful.

Here's a tutorial for how to use a cursor.

Upvotes: 0

AndrewBay
AndrewBay

Reputation: 938

You cannot insert 10 records into one variable.
What you can do however is:

declare @id table (id int)

insert into @id (id)
select EmpId from Employee

select * from @id

What we have done here is create a table variable, which inserts 1 through 10 as seperate rows. You can now do whatever you want with the table.

Upvotes: 20

Ovais Khatri
Ovais Khatri

Reputation: 3211

Try this:

 declare @tmp table(EmpId int)

    insert into @tmp
    select EmpId From Employee

update Employee
set IsActive = 1
where EmpID in (
Select EmpId from @tmp)

Upvotes: 2

Related Questions