How do i call a stored procedure or stored function from sql select statement

Question

I have created following stored user defined it gets executed successfully.

CREATE FUNCTION spherical_distance1(@a float, @b float, @c float , @Lat float, @Lng float)
RETURNS float
AS
BEGIN
    RETURN ( 6371 * ACOS( COS( @a/@b ) * COS( @Lat/@b ) * COS( @Lng/@b - @c/@b )  + SIN( @a/@b ) * SIN( @Lat/@b )))    
END

The problem i am facing is here, when i call stored function spherical_distance1 ,it shows error like 'spherical_distance1' is not a recognized built-in function name.

SELECT *, spherical_distance1(12.9216667, 57.2958, 77.591667, Lat, Lng) AS distance
FROM business3 
WHERE distance < 3
AND StreetName LIKE '%jayanagar   %'
AND Keyword LIKE '%plumbing %'
ORDER BY spherical_distance1(12.9216667, 57.2958, 77.591667, Lat, Lng);

Answer 1

In SQL server, you need to prefix function names with the schema.

Most likely, yours will be dbo, so try calling

select *, 
    dbo.spherical_distance1(12.9216667 ,57.2958,77.591667,Lat ,Lng) as distance 
from 
    business3 
where 
    (( distance < 3 ) and (StreetName like '%jayanagar %') and (Keyword like '%plumbing %' )) 
order by 
     distance -- don't need to repeat the function here

Answer 2

First mistake - it's a USERFUNCTION not a STOREDPROCEUDRE

Second - to call the user function you have to use

SELECT dbo.functionName()

so for your case

SELECT dbo.spherical_distance1(12.9216667, 57.2958, 77.591667, Lat, Lng) AS distance

Answer 3

you need to include "dbo." before function name in your query...