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:18:59 +0000

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






Archive powered by MHonArc 2.6.24.

Top of Page