how can i get only 1 select from a subquery in a function. 1

[rtdvoip]

My problem is as follows,
My sub query in the update of @rate_hier
is returning multiple values. I only need on value returned
at a time. If i try to do a simple join i still get
multiple values. Any thoughts on how I can get around this?
code...
[code/]
Create Function fn_AtoZ_Prices( @cInput_rate AS char(30))
RETURNS @rate_pricing Table (target varchar(30), Call_price money)

Begin
Declare @i int, @x int, @rate_string varchar(20), @found_rate varchar(20)
Declare @n_str int, @conv_rate int, @first int

set @i = len(@cInput_rate)
set @x = 1
set @n_str = @i
set @first = 1

Declare @rate_hier table(target varchar(20),call_price money null)

insert into @rate_hier(target)
select target from call_targets

while @i >= 0
begin
If Substring ( @cInput_rate, @i, 1) Between '0' AND '9'
begin
set @i = @i-1
set @x = @x+1
set @n_str = @i
end
else
begin
set @found_rate = substring(@cInput_rate, @n_str+1,@x-1)
set @n_str = @i-1
set @i = @i-1
set @x = 1
update @rate_hier
set call_price = (select rate_plans.call_price from rate_plans
where rate_plan_index = cast(@found_rate as int)
and rate_plans.target = target)
where target = (select rate_plans.target from rate_plans
where rate_plan_index = cast(@found_rate as int)
and rate_plans.target = target)
end
end
INSERT @rate_pricing
SELECT target, call_price
FROM @rate_hier
RETURN
End
[/code]

 

[zhavic]

Try to use this UPDATE statement:

UPDATE @rate_hier
      SET call_price = rate_plans.call_price
	FROM @rate_hier dest 
	INNER JOIN rate_plans ON rate_plans.rate_plan_index = cast(@found_rate as int) AND 
						     rate_plans.target = dest.target

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[maswien]

Or:

UPDATE @rate_hier SET call_price = rate_plans.call_price
FROM rate_plans
where rate_plans.rate_plan_index = cast(@found_rate as int)
AND rate_plans.target = @rate_hier.target