FreeTDS Development Group

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

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/
--------------------------------------------------------