Search code examples
sqlsql-server-2008t-sqlsql-function

SQL function returns correctly but shows error in Management Studio

I have this function, which although it returns the value correctly, shows an error in the Management Console with the squiggly red underline:

Cannot find either column dbo or the user-defined function or aggregate

The reason this is a problem is that I discovered this to be the cause of a datatype error down the line.

This is the function: 

CREATE FUNCTION CALC_DISTANCE(@A_LAT decimal(9,6), @A_LNG decimal(9,6), @B_LAT decimal(9,6), @B_LNG decimal(9,6))
RETURNS int
AS
BEGIN
    DECLARE @source geography = geography::Point(@A_LAT, @A_LNG, 4326)
    DECLARE @target geography = geography::Point(@B_LAT, @B_LNG, 4326)
    DECLARE @MILES float = 0.00062137
    DECLARE @D int = (SELECT @source.STDistance(@target) * @MILES * 1.08)
    RETURN @D
END
GO

DECLARE @A_LAT decimal(9,6) = '41.60054'
DECLARE @A_LNG decimal(9,6) = '-93.60911'
DECLARE @B_LAT decimal(9,6) = '25.77427'
DECLARE @B_LNG decimal(9,6) = '-80.19366'

SELECT dbo.CALC_DISTANCE(@A_LAT,@A_LNG,@B_LAT,@B_LNG) As 'CALC_DISTANCE (MILES)'

I'm new to SQL functions so this might be a newbie mistake.

Thanks for your help!

Solution

  • If this function has just been added you'll need to "refresh the IntelliSense cache":

    From a query editor window, Edit(Menu)->IntelliSense->Refresh Local Cache. This will, of course, only detect the function if it was added before the cache refresh: the easiest way to ensure that the SSMS editor has the latest information is to declare the function, refresh the cache, and then use the function.

    If it runs, then it's OK and SSMS is just confused (perhaps due to a stale cache, as per above) - the red squiggles are a client-side artifact only and do not always reflect the query execution.