simple looping question

Question

simple looping question

Ade_1

SSC Enthusiast

Points: 175
More actions
April 3, 2014 at 9:22 am

#396535

Hi
I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration. Please could you tell me where I'm going wrong.
declare @i int,@output varchar(max)
set @i = 1
while @i < 10
begin
set @output = @output + convert(varchar(max),@i) + ','
print @output
set @i = @i +1
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

WADRIAN68 (4/3/2014)
Hi
I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration. Please could you tell me where I'm going wrong.
declare @i int,@output varchar(max)
set @i = 1
while @i < 10
begin
set @output = @output + convert(varchar(max),@i) + ','
print @output
set @i = @i +1
end

The first place you went wrong is by using a loop for this. I don't know exactly what you are trying to accomplish but a loop is not the answer. Maybe you are trying to create a comma separated list of values from a table? If you can explain what you are actually trying to do we can help you find a fast set based solution instead of looping.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ade_1 SSC Enthusiast Points: 175 More actions · Answer 2

Hi

Yes a comma separated list of values from multiple rows is essentially what I'm trying to do and a set based approach would be great.

That said I'm still interested in why the loop does not produce the output I was expecting.

I appreciate a loop is not the most efficient way but I'm up against time now just trying to get it done any which way.

Thanks for your help so far.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

The problem with your loop (other than being a loop:-P) is that you didn't initialize the @output variable. You're adding values to NULL and that's giving you NULL. If you assign an empty string to your variable, you'll get it done.

To change this method, to a set based one, you could read the following article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ade_1 SSC Enthusiast Points: 175 More actions · Answer 4

Sorry me again

I've managed to build a comma separated string using a set based approach. but I'm struggling to perform an update to another table using the result.

Is it possible to re-code the following to enable me to pass the value stored in @List, back to my t1 table? (I know what's shown below is not complete but conceptually what I'm trying to do is use the @list variable in my update statement or something to that effect).

declare @list varchar(max)

update rpt.feehistory t1

set t1.fee_history =

select @List = coalesce(@list + ',','') + cast(type as varchar(10)) + cast(date as varchar(25))

from fee where customerid = 6018) a

Ade_1 SSC Enthusiast Points: 175 More actions · Answer 5

Thanks for you help on this.

I've moved the update table topic to

http://www.sqlservercentral.com/Forums/Topic1558119-3077-1.aspx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

WADRIAN68 (4/3/2014)

Thanks for you help on this.

I've moved the update table topic to

http://www.sqlservercentral.com/Forums/Topic1558119-3077-1.aspx%5B/quote%5D

http://www.sqlservercentral.com/Forums/Topic1558119-3077-1.aspx