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.
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.
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)
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
;
If processing rows one by one is necessary, cursor will be useful.
