Skip to Content.
Sympa Menu

freetds - RE: [freetds] dbnextrow() question

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] dbnextrow() question
  • Date: Mon, 21 Mar 2005 15:27:25 +0100

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
>




Archive powered by MHonArc 2.6.24.

Top of Page