calling functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

calling functions

Author

Topic

kien
Starting Member

27 Posts

Posted - 2002-07-12 : 11:41:51

How's it going everyone?
I'm not too familiar with calling user defined functions. Can someone help me out here.

I have a really messy data manipulation code below (sorry), where i am trying to call on the function "Distance".
I'm running the code below in Query Analyzer. The syntax checks out, but there is an error during run time (see error message below).

I pasted the "distance" function in user defined function section of enterprise manager. Do i need to prepare anything before i run it? Or is my syntax just wrong...

Thanks for your tip

-----------------------

DECLARE @CityList varchar(7000)
DECLARE @longitude decimal(10,7)
DECLARE @latitude decimal(9,7)

DECLARE city_cursor CURSOR FOR
SELECT Longitude,
Latitude
FROM tblRecordSet2
OPEN city_cursor
FETCH NEXT FROM city_cursor INTO @longitude, @latitude

WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (select Longitude, Latitude, Query
from tblRecordSet2
where (Query IS NULL) AND Longitude = @longitude AND Latitude = @latitude)
BEGIN
SELECT @CityList = COALESCE(@CityList + ' ', '') + isnull(querycity, '')
FROM (SELECT t1.FullName,
t1.DLONG,
t1.DLAT,
t2.FullName AS querycity
FROM GeoRef.dbo.tblDataAS t1
INNER JOIN GeoRef.dbo.tblDataAS t2 ON (t2.FeatureCode=14 and (select dbo.Distance(t1.DLAT, t1.DLONG, t2.DLAT, t2.DLONG))<100)) AS t3

----Above is where i try to call the function distance!----

WHERE DLONG = @longitude
AND DLAT = @latitude

UPDATE tblRecordSet2
SET Query = @CityList
WHERE longitude = @longitude
AND latitude = @latitude

SET @CityList = Null
END

FETCH NEXT FROM city_cursor INTO @longitude, @latitude
END
CLOSE city_cursor
DEALLOCATE city_cursor

-----------The error from Query Analyser is
Server: Msg 913, Level 16, State 8, Line 18
Could not find database ID 104. Database may not be activated yet or may be in transition.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-12 : 11:51:26

kien, I'm not sure about your error, but I wanted to mention that if you look back at your original post on this issue, I provided you with a set based solution to building the city list string. It will outperform this cursor, for sure...

Have you using the function in a stand alone manner? Do you get the same error?

<O>

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-12 : 13:40:23

I'm saying nothing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources