February 15, 2007 at 11:25 am
Can anyone explain how this works? The result (in @result) contains a concatenation of all the values of field1. This implies that the SELECT statement is causing some sort of loop. Am I missing something?
create table #temp (field1 char(1))
insert into #temp values ('A')
insert into #temp values ('B')
insert into #temp values ('C')
insert into #temp values ('D')
declare @result varchar (10)
set @result = ''
select @result = @result + field1 from #temp
print @result
drop table #temp
February 15, 2007 at 11:39 am
from the help file:
SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.
since you are storing the value for each record coming across into the variable and then adding the next record, that is why you see the variable printing as ABCD.
If you did not have @result = @result + field1 and just had @result = field1, the result would just be D
February 15, 2007 at 12:02 pm
"since you are storing the value for each record coming across into the variable..."
Since I am *not* storing the value for each record, I guess SQL Server is.
Which is another way of saying, yes, each row is causing the value to be appended which implies that there is a loop occuring there.
I could have made the same thing happen with a cursor. But then people would say not to use cursors because they are inefficient. However, there is no way of knowing (as far as I can tell) HOW this is being done internally. Maybe Microsoft has coded the cursor for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply