Solved

exec scaler-value function sql server 2005

Posted on 2007-03-28
9
316 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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