while loop question

Question

while loop question

vani_r14

SSCrazy

Points: 2843
More actions
February 11, 2010 at 8:39 pm

#96892

Hi all - I have two tables and two loops in question...
I am trying to do the following - i have a table with the column num and mobileno. Num has been generated automatically for all rows in the table. I need to join all the mobileno's seperated by semi columns. and I have done it using a loop by terminating it manually. I am trying to automate it and am stuck in here.
Table Data
Num Mobile
1 0458974598456
2 0235744598744
3 02354748782121
4 03696998845555
5 04789745269871
and so on and this can be any no's. I have a file with 100 rows.
This is the logic behind my loop
First work out the no of times the loop has to repeat itself by using the following -
declare @C int; set @C = (select floor(max(num)/30)+1 from mobile);
declare @d int; set @d = 1;
Then I have a variable called cnt which matches the num column in the mobile table.
declare @cnt int; set @cnt = 1;
I have tried to control the outside loop using the logic of how many times to run and inside loop using the counter.
Here is the complete loop
declare @C int; set @C = (select floor(max(num)/50) from mobile);
declare @d int; set @d = 1;
declare @cnt int; set @cnt = 1; declare @mobno varchar(8000);
select @C, @d, @cnt
while (@d <= @C )
begin
while (@cnt <= (select min(num) + 50 from mobile))
begin
select @mobno = coalesce(@mobno, ';', '')+mobile1 from mobile
where num = @cnt
set @cnt = @cnt + 1
end
insert into temp1 (mobileno) select @mobno
delete from mobile where num <@cnt
set @d = @d+1
end
The coalesced no will be stored in the table temp1 which is then saved as file etc. This works fine when i manually set the no in the inside while loop
while (@cnt <= 50) (i.e. the first time it copies data in rows 1 - 50 and then copies data in rows 51 - 100 into temp1)
and however if i change this back to the while (@cnt <= (select min(num) + 50 from mobile)) it coalesces the same rows of data over and over again in temp1 (i.e. rows 1 - 50) it does not go past that...
can some one please point me in some direction on where am going wrong
thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

select stuff((select ';' + Mobile from [YourUn-namedTable] order by Num FOR XML PATH('')),1,1,'')

If this doesn't get you what you're looking for, please take a look at the first link in my signature, then post your table DDL and DML in a readily consumable format, as explained in that link. Also, for the sample data supplied, show what you expect as a result.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vani_r14 SSCrazy Points: 2843 More actions · Answer 2

Hi all

If any one is interested this is how I fixed my own while loop... It might not be the best way but it works out ... please find attached is the file

1. Import data into a table in sql server with SSIS package and add a column called Num to number each row individually

2. Format the No's into a proper format

3. Run the Loop to get the numbers in the form of email address in groups of 150 each...

Thanks for your help Wayne. I used the function you showed in your solution, but i wanted to learn so I tried this way as well and both work

Thanks again

Vani