Skip to Content.
Sympa Menu

freetds - RE: [freetds] dbnextrow() question

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] dbnextrow() question
  • Date: Mon, 21 Mar 2005 14:26:22 -0000

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/
--------------------------------------------------------




Archive powered by MHonArc 2.6.24.

Top of Page