Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

exec scaler-value function sql server 2005

Posted on 2007-03-28
9
325 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

837 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