FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

Or use functions instead...


> 
> 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/
> --------------------------------------------------------
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>