FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Alas, no.
you have to put the result in a  variable first...
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 14:18
To: FreeTDS Development Group
Subject: Re: [freetds] dbnextrow() question


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 @radiusEarth integer
>>>>   declare @delta_lat    float
>>>>   declare    @delta_lon    float
>>>>   declare @intermed_a    float
>>>>   declare @intermed_c    float
>>>>   declare @result        float
>>>>  
>>>>       set @radiusEarth = 6367
>>>>  
>>>>   @delta_lon = AsRadians(@prev_lon) - AsRadians(@longitude);
>>>>   @delta_lat = AsRadians(@prev_lat) - AsRadians(@latitude) ;
>>>>  
>>>>   @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
>>> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^|
>>>                 + cos(Lat_radians)                 * 
>>> cos(lat_radians)                 * ( (1 - cos(2 * ((lon_radians -
>>> Lon_radians) / 2))) / 2 )
>>>                 as intermed_a
>>>             from (    -- radians
>>>                 select    PlaceName
>>>                     , (PI() / 180) * @lat
>>> as lat_radians
>>>                     , (PI() / 180) * PC.Latitude
>>> as Lat_radians
>>>                     , (PI() / 180) * @lon
>>> as lon_radians
>>>                     , (PI() / 180) * PC.Longitude
>>> as Lon_radians
>>>                 FROM PostCodes as PC
>>>             ) as Radians
>>>         ) 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
>> the intended recipient, please contact the sender immediately by
reply
>> 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
>> receive further e-mail correspondence from the sender, please "reply"
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, 
>> copy, retain or redistribute it. Click here for important additional 
>> 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


_______________________________________________
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, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail.     http://www.ml.com/email_terms/
--------------------------------------------------------