Call of a userdefined function

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 2005 Forums

Transact-SQL (2005)

Call of a userdefined function

Author

Topic

Nroblex
Starting Member

17 Posts

Posted - 2010-01-19 : 09:59:30

Hello gurus!

I have a userdefined tablefunction called WGS84_RT90 that converts a geo point in WGS84 to RT90 returning a table the call looks like this

SELECT * FROM DBO.WGS84_RT90('57.6517','11.8832') and the output will then be a table like this:

RTLatitude RTLongitude
1740892,377 6480478,43527

This is OK.

My Question is how to call this on a whole table e.g with values like
this ('57.6517','11.8832'). My purpose is to convert two columns in a very large table, by calling this function.

Many Thanks in advance.

Nroblex

Nroblex
Starting Member

17 Posts

Posted - 2010-01-19 : 10:21:54

I think i found it out my self...

SELECT
gpsLatitude, gpsLongitude,
(SELECT RTLatitude FROM dbo.WGS84_RT90(gpsLatitude, gpsLongitude)) as RTLatitude,
(SELECT RTLongitude FROM dbo.WGS84_RT90(gpsLatitude, gpsLongitude)) as RTLongitude

FROM [positiontable]

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:23:26

I think the answer is to use CROSS APPLY, but I've never used it, so I can't reliably tell you the syntax.

If I've got that right hopefully you can find out how, or someone who knows will be along shortly ...

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:29:35

Your way will work, might be a bit inefficient though. Probably fine for a one-off calc-and-Update type process.

Its a snag when you need a UDF to return more than one value - a table is the only way (there are no OUTPUT parameters to UDFs, for example)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-19 : 10:45:08

Kristen is referring to something like this:

SELECT
   gpsLatitude, gpsLongitude,
   f.RTLatitude , f.RTLongitude
from
FROM [positiontable]
cross apply
(  select RTLatitude , RTLongitude from
   DBO.WGS84_RT90(gpsLatitude, gpsLongitude)
) as f