"A domain error ocurred" in a sp???

[olemma]

I wrote a sp that executes another sp in it. When I test the "main" sp, the result that I get is "A domain error ocurred". I tested the "inner" sp seperately and it works fine (sending fixed valued or variables). If within the "main" sp I call the "inner" sp with fixed values, it works. But if I call it using variables (assigned from the fetch of a row of a cursor), it gives me this error. This is the first time I get this error and I could find any information on it. Does anyone know what it means? Please help!!!

 

[tlbroadbent]

Can you post the code from the two SPs? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[olemma]

I'm using several arithmetic funcions. I am trying force positive arguments but I still have the error. I couldn't use T-SQL Debbuger because it gave me an "EXECUTE permission denied on object 'sp_sdidebug', database 'master', owner 'dbo'" error. Now i combined both sp in one single one. It's really not that complicated but combining both sp didn't work either. The sp is suppose to determine the distance from a caller to a target number (using zip codes) and keep a count of how many calls are withing less than 10 miles, 30, 60 or greater. This is how the combined sp looks like:
CREATE PROCEDURE sp_distance @date1 datetime, @date2 datetime,@tcall10 int OUTPUT, @tcall30 int OUTPUT,@tcall60 int OUTPUT,@tcallex int OUTPUT, @tcalls int OUTPUT
AS
DECLARE @zip char(5),
@anizip char(5),
@tph char(10),
@lat numeric(18,9),
@lon numeric(18,9),
@anilat numeric(18,9),
@anilon numeric(18,9),
@calls int,
@bldg char(10),
@pct10 numeric(18,9),
@pct30 numeric(18,9),
@pct60 numeric(18,9),
@pctex numeric(18,9),
@bldgc int,
@dist numeric(18,9)

declare @mpm numeric(18,9), @rad numeric(18,9), @dlat numeric(18,9), @dlon numeric(18,9), @a numeric(18,9), @d numeric(18,9), @lt1 numeric(18,9), @lt2 numeric(18,9), @ln1 numeric(18,9), @ln2 numeric(18,9), @na numeric(18,9)
set @rad = 6367000
set @mpm = 1609.344 /* meters per mile*/

SET ANSI_DEFAULTS ON
declare elsrep2 cursor for select
sw.zip as anizip,
sw.latitude as anilat,
sw.longitude as anilon,
call.t_num as tph,
count(*) as calls,
sw1.zip as pzip,
sw1.latitude as lat,
sw1.longitude as long
from
call_cars call,
phone sw,
phone sw1
where
substring(call.ani, 1, 6 ) = (sw.npa+sw.nxx) and
call.date between @date1 and @date2 and
substring(call.t_num, 1, 6 ) = (sw1.npa+sw1.nxx)
group by call.t_num, sw1.zip,sw1.latitude,sw1.longitude,sw.zip,sw.latitude,sw.longitude;

select @bldg = '0'
select @bldgc = 0
open elsrep2
fetch next from elsrep2 into @anizip,@anilat,@anilon,@tph,@calls,@zip,@lat,@lon
while(@@fetch_status = 0)
begin
if ltrim(rtrim(@bldg)) <> ltrim(rtrim(@tph))
begin
select @bldgc= @bldgc + 1
end
select @bldg = @tph
if ltrim(rtrim(@anizip))<>'0' and @anizip<> null and ltrim(rtrim(@zip))<>'0' and @zip<>null
begin
set @tcalls = @tcalls + @calls
if @anilat>0 and @anilon>0 and @lat>0 and @lon>0
begin
set @lt1 = (@anilat*pi() )/180
set @lt2 = (@lat*pi())/180
set @ln1 = (@anilon*pi())/180
set @ln2 = ( @lon*pi())/180

set @dlat = @lt2 - @lt1
set @dlon =@ln2 - @ln1
set @dlat= +@dlat
set @dlon= +@dlon
set @a = ( sin(@dlat / 2 ) ) * 2 + (cos( @lt1 ) * cos(@lt2 ) * ( sin(@dlon / 2 ) ) * 2)
set @na= 1- @a
set @na= +@na
set @a=+@a

set @d = 2 * atan( sqrt(@a ) / sqrt( @na ) )

set @dist=(@rad *@d )/ @mpm

if @dist > 60
set @tcallex = @tcallex + @calls
else if @dist > 30
set @tcall60 = @tcall60 + @calls
else if @dist > 10
set @tcall30 = @tcall30 + @calls
else
set @tcall10 = @tcall10 + @calls
end
end
fetch next from elsrep2 into @anizip,@anilat,@anilon,@tph,@calls,@zip,@lat,@lon
end
close elsrep2
deallocate elsrep2
return
GO