[freetds] dbnextrow() question

craigs s.craig at andronics.com
Mon Mar 21 08:52:53 EST 2005


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




More information about the FreeTDS mailing list