Best Coding practice for Variable Value assignment

Posted by Bandi under Sql Server category on | Points: 40 | Views : 319
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

Comments or Responses

Login to post response