DAVNovak (5/3/2013)
Try:Create Function Get_XY_Coordinates (@i_Pupil_Number int)
Returns int
Begin
Declare @iXYCoord int
SELECT @iXYCoord = sab.address_id
From sps_address_bridge sab inner join sps_stud_assignment_address saa on sab.street_number = saa.street_number
And sab.full_street_name = saa.street_name
And sab.postal_code = saa.postal_code
Where saa.pupil_number = i_pupil_number
Order by CASE WHEN sab.Housemodifier IS NULL THEN '0' ELSE 'Z' END, sab.x_coordinate, sab.y_coordinate
Return @iXYCoord
END
The above query runs very slow, i think the reason is in the order by there is the case statement,
can anyone help make this query more efficient, but still get the same result?
Thanks