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 14:36:24 +0000

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






Archive powered by MHonArc 2.6.24.

Top of Page