Always make sure that the SELECT query returns single value when you write code that assigns value to a variable. Otherwsie the variable will have wrong/unexpected data
create table #t(id int, names varchar(100))
insert into #t(id,names)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3' union all
select 4,'test4' union all
select 5,'test5'
declare @id int
set @id=(select id from #t where names='test1')
select @id as id
select @id=id from #t where names='test1'
select @id as id
-- Error
declare @id1 int
set @id1=(select id from #t)
/*
ERROR message
Msg 512, Level 16, State 1, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
*/
select @id1 as id
declare @id2 int
select @id2=id from #t
-- It will avoid error message by assigning last ID from the table. but this may leads to incorrect result. So make sure that your query have to return only one record while assigning value to variable from a query
select @id2 as id
declare @name varchar(60) = ''
select @name= @name + names + ', ' from #t
select @name as names
drop table #t