Soluga
asked on
exec scaler-value function sql server 2005
Hi, I am trying to exec a scaler-function from a stored proc but every time I test the stored proc it says,
Msg 208, Level 16, State 3, Procedure getPostCodes, Line 19
Invalid object name 'DistanceBetween'.
This is my function top....
ALTER FUNCTION [dbo].[DistanceBetween] (@Lat1 as real,
@Long1 as real, @Lat2 as real, @Long2 as real)
in my proc I just say select * from DistanceBetween and pass in the four values
This is sql server 2005
Grateful for any help
Msg 208, Level 16, State 3, Procedure getPostCodes, Line 19
Invalid object name 'DistanceBetween'.
This is my function top....
ALTER FUNCTION [dbo].[DistanceBetween] (@Lat1 as real,
@Long1 as real, @Lat2 as real, @Long2 as real)
in my proc I just say select * from DistanceBetween and pass in the four values
This is sql server 2005
Grateful for any help
you must specify the objectowner whenver u calls a UDF
select * from dbo.DistanceBetween
select * from dbo.DistanceBetween
If this is a scalar function (does not return a table) than you cannot include it in a FROM clause, but rather as part of the SELECT clause. If on the other hand, it does in fact return a table than don't forget the parameters and user name as in:
select * from dbo.DistanceBetween(@Param 1, @Param2, @Param3, @Param4)
select * from dbo.DistanceBetween(@Param
ASKER
Right, so I need to set it up as Table-value function then?
ASKER
Here is my function as it is, which kind of function should I set it up as? I want to be able to call the function from a procedure then populate a temp table with the results, the function will be part of a loop. Knew I should have stuck to 2000.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[DistanceBetween] (@Lat1 as real,
@Long1 as real, @Lat2 as real, @Long2 as real)
RETURNS real
AS
BEGIN
DECLARE @dLat1InRad as float(53);
SET @dLat1InRad = @Lat1 * (PI()/180.0);
DECLARE @dLong1InRad as float(53);
SET @dLong1InRad = @Long1 * (PI()/180.0);
DECLARE @dLat2InRad as float(53);
SET @dLat2InRad = @Lat2 * (PI()/180.0);
DECLARE @dLong2InRad as float(53);
SET @dLong2InRad = @Long2 * (PI()/180.0);
DECLARE @dLongitude as float(53);
SET @dLongitude = @dLong2InRad - @dLong1InRad;
DECLARE @dLatitude as float(53);
SET @dLatitude = @dLat2InRad - @dLat1InRad;
/* Intermediate result a. */
DECLARE @a as float(53);
SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad)
* COS (@dLat2InRad)
* SQUARE(SIN (@dLongitude / 2.0));
/* Intermediate result c (great circle distance in Radians). */
DECLARE @c as real;
SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a));
DECLARE @kEarthRadius as real;
/* SET kEarthRadius = 3956.0 miles */
SET @kEarthRadius = 6376.5; /* kms */
DECLARE @dDistance as real;
SET @dDistance = @kEarthRadius * @c;
return (@dDistance);
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[DistanceBetween] (@Lat1 as real,
@Long1 as real, @Lat2 as real, @Long2 as real)
RETURNS real
AS
BEGIN
DECLARE @dLat1InRad as float(53);
SET @dLat1InRad = @Lat1 * (PI()/180.0);
DECLARE @dLong1InRad as float(53);
SET @dLong1InRad = @Long1 * (PI()/180.0);
DECLARE @dLat2InRad as float(53);
SET @dLat2InRad = @Lat2 * (PI()/180.0);
DECLARE @dLong2InRad as float(53);
SET @dLong2InRad = @Long2 * (PI()/180.0);
DECLARE @dLongitude as float(53);
SET @dLongitude = @dLong2InRad - @dLong1InRad;
DECLARE @dLatitude as float(53);
SET @dLatitude = @dLat2InRad - @dLat1InRad;
/* Intermediate result a. */
DECLARE @a as float(53);
SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad)
* COS (@dLat2InRad)
* SQUARE(SIN (@dLongitude / 2.0));
/* Intermediate result c (great circle distance in Radians). */
DECLARE @c as real;
SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a));
DECLARE @kEarthRadius as real;
/* SET kEarthRadius = 3956.0 miles */
SET @kEarthRadius = 6376.5; /* kms */
DECLARE @dDistance as real;
SET @dDistance = @kEarthRadius * @c;
return (@dDistance);
END
what excatly u r trying to do inside the fuction ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good stuff aneeshattingal, that got it, thanks everyone for the help.
ASKER
By the way, the proc calculates the distance between four given points of latitude and longitude, so I pass in the values... Select dbo.DistanceBetween (57.135, -2.117, 57.138, -2.092) which will give me the distance between two different places on the planet, so long as you know the starting latitude, longitude and the ending latitude and longitude.
Good eh!
Good eh!
select * from dbo.DistanceBetween