FreeTDS Development Group

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

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
>