why is my loop not looping?

[rtdvoip]

Ok it is late in the day but can someone help?

declare @x int, @prefix char(30)
declare @breakout_no char(10)

set @breakout_no = '19999999'
set @prefix = ''
set @x = len(@breakout_no)

print @prefix
while @prefix <> @breakout_no
begin

print @x
set @breakout_no = substring (@breakout_no,1,@x)
print @breakout_no
set @prefix = (select prefix
from rate_plan
where prefix = @breakout_no and rate_plan_index = 1)
print 'prefix'
print @prefix
set @x = @x -1
end
select call_price
from rate_plan
where prefix = @prefix and rate_plan_index = 1

thanks a million

 

[LogicalmanUS]

rtdvoip,

Two things come to mind in this code.

Firstly, the loop is designated by the comparison of two Chars, @breakout_no and @prefix. On the first loop you set @prefix equal to a selection of prefix where prefix = @breakout_no, therefore, in the first loop, @prefix is set to a selection equal to @breakout_no.
Secondly, char data types are fixed length. When used in tables, unless NULL is specified, then the values are padded right, e.g. when 'Test' is assigned in a char(10) column, it is stored as 'char '. This could account for a mismatch. I haven't tested that out, as yet, but it is a known characteristic (no pun intended).
You might like to change the data types to varchar, this simply stores the actual value inserted.

Logicalman