FreeTDS Development Group

[craigs <s.craig AT andronics.com>]

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
>