FreeTDS Development Group

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

unfortunately i cant, using 7.0 which doesnt support UDF's

ZIGLIO, Frediano, VF-IT wrote:

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