Skip to Content.
Sympa Menu

freetds - Re: [freetds] dbnextrow() question

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: craigs <s.craig AT andronics.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] dbnextrow() question
  • Date: Mon, 21 Mar 2005 13:52:53 +0000

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






Archive powered by MHonArc 2.6.24.

Top of Page