Solved

exec scaler-value function sql server 2005

Posted on 2007-03-28
9
295 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
 
LVL 1

Author Comment

by:Soluga
ID: 18810575
Right, so I need to set it up as Table-value function then?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard 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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now