6

I have a function [dbo].[FN_CalcAerialDistance] which accepts 4 parameters and returns its result. Now that 4 parameters come from the same table, I do not want to hit Database multiple times to bring same data. I have tried:

SELECT [dbo].[FN_CalcAerialDistance]
    (
       (select latitude from loc.locations where place_name = 'Delhi'),
       (select longitude from loc.locations where place_name = 'Delhi'),
       (select latitude from loc.locations where place_name = 'Mumbai'),
       (select longitude from loc.locations where place_name = 'Mumbai')
    )

Any way to optimise the code? I have also tried to access table datatype like an array

12

As a complement to Edgars answer you can do it without declaring any variables as:

SELECT dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name = 'Delhi'
  AND y.place_name = 'Mumbai' ;

I wouldn't expect it to perform any better though.

  • I like this because it clearly expresses the intent: for each x/y pair of locations, calculate the aerial distance. – mbeckish Jan 19 '18 at 17:11
  • Still way cleaner and easier to read. – jpmc26 Jan 20 '18 at 0:22
  • Thanks a lot @lennart! Earlier I used loops to process data, now cross join has solved it. You can see my procedure (in the question description) : link – Gour Gopal Jan 20 '18 at 8:01
8

This is the only thing I could think of. You could try this as a basis:

DECLARE
    @p1 DECIMAL(12, 9),
    @p2 DECIMAL(12, 9),
    @p3 DECIMAL(12, 9),
    @p4 DECIMAL(12, 9) ;

SELECT @p1 = latitude, @p2 = longitude 
FROM loc.locations 
WHERE place_name = 'Delhi';

SELECT @p3 = latitude, @p4 = longitude 
FROM loc.locations 
WHERE place_name = 'Mumbai';

SELECT dbo.fn_calcaerialdistance (@p1, @p2, @p3, @p4) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.