# [freetds] dbnextrow() question

craigs s.craig at andronics.com
Mon Mar 21 09:18:13 EST 2005

```Does anyone know if i can call a math function while executing a stored
procedure

for example can i do this

EXEC GetMin @x = @my_lon / 2, @y = sqrt( @my_lat ) @ResultOut = @my_min
OUTPUT

Shaun.

craigs wrote:

> Cheers Bill,
>
> thats what i was looking for, probably going to be testing today so it
> should be interesting!
>
> Thompson, Bill D (London) wrote:
>
>> Shaun,
>>
>> DECLARE @my_x float,
>>        @my_y float
>> DECLARE @my_min float
>>
>> SELECT @my_x = ..., @my_y = ...
>>
>> EXEC GetMin @x = @my_x, @y = @my_y @ResultOut = @my_min OUTPUT
>>
>>
>> Bill
>>
>> -----Original Message-----
>> From: freetds-bounces at lists.ibiblio.org
>> [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of craigs
>> Sent: 21 March 2005 13:19
>> To: FreeTDS Development Group
>> Subject: Re: [freetds] dbnextrow() question
>>
>>
>> Guys, just got back to working on this problem.
>>
>> Can someone show me how to call a stored procedure(nested) inside my
>> main stored procedure, searched damn web looking for an example and
>> having no luck, for example here is the function i want to call
>> inside my main stored procedure.
>>
>> CREATE  procedure GetMin
>> (
>>    @x double,
>>    @y double,
>>    @ResultOut double OUTPUT
>> )
>> as
>>    if ( @x <= @y )
>>        Set @ResultOut = @x
>>    else
>>        Set @ResultOut = @y
>>
>> Return
>>
>> can i do the following:
>>
>> declare @GetMinResult        double
>> @GetMinResult = GetMin( 1, sqrt( @intermed_a ))
>>
>> or do i have to do something like this:
>>
>> exec GetMin( 1, sqrt( @intermed_a )), @GetMinResult OUTPUT
>>
>> where @GetMinResult would hold the result of the procedure?
>>
>> Any help would be much appreciated!
>>
>> Shaun.
>>
>> Lowden, James K wrote:
>>
>>
>>
>>>> From: craigs
>>>> Sent: Monday, March 14, 2005 10:13 AM
>>>>
>>>> Bill,
>>>>
>>>> Could you have a look at my first attempt, first time writing
>>>> stored procedures/functions and im sure i've got something wrong.
>>>>
>>>> CREATE PROC GetPostcodeRecord ( @my_lat float, @my_long float )
>>>> AS
>>>> BEGIN
>>>>     SELECT TOP 1 PlaceName, Town, Country, Latitude, Longitude
>>>>     FROM  PostCodes PC
>>>>     WHERE LatLonDistance( @my_lat, @my_lon, PC.Latitude,
>>>>
>>>>
>>>
>>> PC.Longitude
>>>
>>>
>>>
>>>> ) < 1
>>>>
>>>>
>>>
>>> "Top 1": Do you want just any row?  You might want an ORDER BY clause,
>>> but ISTM you're looking for the PlaceName with the minimum distance....
>>>
>>> To answer your UDF question: yes, it's a feature of SQL Server 2000.
>>> You can nest stored procedures, and you can get almost the same
>>> modularity using OUTPUT parameters.  Just remember to use the OUTPUT
>>>
>>
>> tag
>>
>>
>>> both in the procedure body and when you call it.
>>> IMHO, you're better off with a single procedure, because (lacking
>>> functions) the code will be easier to follow than with OUTPUT
>>> parameters.  I've attached one that might do the job.
>>>
>>>
>>>
>>>
>>>> CREATE FUNCTION AsRadians( @x float )
>>>>
>>>>
>>>
>>> There is a built-in PI() function, no?
>>>
>>>
>>>
>>>
>>>> CREATE FUNCTION Arcsin( @x float )
>>>>
>>>>
>>>
>>> Does the function asin() do what you want?
>>>
>>>
>>>
>>>
>>>> CREATE FUNCTION LatLonDistance(@latitude float, @longitude float,
>>>> @prev_lat float, @prev_lon float)
>>>> RETURNS float AS
>>>> BEGIN
>>>>   declare @delta_lat    float
>>>>   declare    @delta_lon    float
>>>>   declare @intermed_a    float
>>>>   declare @intermed_c    float
>>>>   declare @result        float
>>>>
>>>>
>>>>
>>>>   @intermed_a = Sin2(@delta_lat / 2) + cos( AsRadians( @latitude ))
>>>>
>>>>
>>>
>>> *
>>>
>>>
>>>
>>>> cos( AsRadians( @prev_lat )) * Sin2( @delta_lon / 2);
>>>>
>>>>   @intermed_c = 2 * Arcsin( GetMin( 1, sqrt( @intermed_a )));
>>>>
>>>>   @result = @radiusEarth * @intermed_c;
>>>>
>>>>   return @result;
>>>>
>>>> END
>>>>
>>>>
>>>
>>> So here goes.  I'm just trying to restate your query.  I'm sure it can
>>> be simplified; it may even be wrong.
>>> It may also be quite slow (although still faster than transmitting the
>>> whole table to the client) because the server has no useful index an
>>> must examine every row.  To speed it up, the best thing would be to
>>> restrict the number of rows examined.  For example, would it be the
>>>
>>
>> case
>>
>>
>>> that 90% of the time the PlaceName will be located at +/- 1 degree (or
>>> some other threshold)?  If so, you could restrict the innermost query
>>> that way, and, if it fails to find one, fall back to scanning the whole
>>> table.
>>> Another optimization would be to cache the results in a global
>>>
>>
>> temporary
>>
>>
>>> table.  That way, once a PlaceName is determined as the solution for a
>>> given latitude/longitude pair, it can be re-retrieved without being
>>> recalculated.
>>> HTH.
>>> --jkl
>>>
>>>
>>> CREATE PROC GetPostcodeRecord
>>>       @lat float
>>>     , @lon float
>>> as BEGIN
>>>     create table #distances (PlaceName varchar(30), Distance float)
>>>
>>>     insert #distances
>>>     select PlaceName, distance
>>>     from ( -- distances
>>>         select      PlaceName
>>>             , 6367 * 2             * asin(    case    when 1.0 <
>>> sqrt(intermed_a)                         then 1.0
>>>                         else sqrt(intermed_a)                     end
>>>               ) as distance
>>>         from ( -- intermediate "a"
>>>             select    PlaceName
>>>                 , ( (1 - cos(2 * ((lat_radians -
>>> Lat_radians) / 2))) / 2 )
>>>                 --  |            |^^^^^^^^^^ 1/2 delta
>>> ^^^^^^^^^^|     |
>>>                 --  |^^^^^^^^^^^^^ Sin2
>>> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^|
>>> Lon_radians) / 2))) / 2 )
>>>                 as intermed_a
>>>                 select    PlaceName
>>>                     , (PI() / 180) * @lat
>>>                     , (PI() / 180) * PC.Latitude
>>>                     , (PI() / 180) * @lon
>>>                     , (PI() / 180) * PC.Longitude
>>>                 FROM PostCodes as PC
>>>         ) as intermediates
>>>     ) as Distances
>>>     where distance < 1
>>>
>>>     SELECT PlaceName, Town, Country, Latitude, Longitude
>>>     FROM PostCodes     WHERE PlaceName in (
>>>         select min(PlaceName)
>>>         from #distances         where Distance = (
>>>             select min(Distance)             from #distances
>>>         )
>>>     )
>>> END
>>>
>>> -----------------------------------------
>>> The information contained in this transmission may contain privileged
>>>
>>
>> and confidential information and is intended only for the use of the
>> person(s) named above. If you are not the intended recipient, or an
>> employee or agent responsible for delivering this message to the
>> intended recipient, any review, dissemination, distribution or
>> duplication of this communication is strictly prohibited. If you are not
>> e-mail and destroy all copies of the original message. Please note that
>> we do not accept account orders and/or instructions by e-mail, and
>> therefore will not be responsible for carrying out such orders and/or
>> instructions.
>>
>>
>>> If you, as the intended recipient of this message, the purpose of which
>>>
>>
>> is to inform and update our clients, prospects and consultants of
>> developments relating to our services and products, would not like to
>> the sender indicating your wishes.  In the U.S.: 1345 Avenue of the
>> Americas, New York, NY 10105.
>>
>>
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS at lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>>
>>>
>>
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS at lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>> --------------------------------------------------------
>>
>> If you are not an intended recipient of this e-mail, please notify
>> the sender, delete it and do not read, act upon, print, disclose,
>> terms relating to this e-mail.     http://www.ml.com/email_terms/
>> --------------------------------------------------------
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS at lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

```