?
Solved

exec scaler-value function sql server 2005

Posted on 2007-03-28
9
Medium Priority
?
349 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Soluga
9 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18809572
You need to specify the schema name, eg:

select * from dbo.DistanceBetween
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18809575
you must specify the objectowner whenver u calls a UDF

select * from dbo.DistanceBetween
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18810190
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(@Param1, @Param2, @Param3, @Param4)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Soluga
ID: 18810575
Right, so I need to set it up as Table-value function then?
0
 
LVL 1

Author Comment

by:Soluga
ID: 18810626
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18810631
what excatly u r trying to do inside the fuction ?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 18810669
oops, i think we posted at the same time,
Your function is returning a scalar value, so you just need a select statement

select dbo.distancebetween(10,12,33,44)
0
 
LVL 1

Author Comment

by:Soluga
ID: 18810701
Good stuff aneeshattingal, that got it, thanks everyone for the help.
0
 
LVL 1

Author Comment

by:Soluga
ID: 18810732
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!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question