Referenceing User Defined Functions

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

SQL Server Development (2000)

Referenceing User Defined Functions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-07 : 08:09:58

Elie writes "Is there a way to reference or call a user defined function in Microsoft SQL Server without prefixing it with the owner?

Thanks"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-07 : 08:12:46

From Books Online:

quote:
Calling User-Defined Functions

When calling a scalar user-defined function, you must supply at least a two-part name:

SELECT *, MyUser.MyScalarFunction()
FROM MyTable

Table-valued functions can be called by using a one-part name:

SELECT *
FROM MyTableFunction()

However, when you call SQL Server built-in functions that return a table, you must add the prefix :: to the name of the function:

SELECT * FROM ::fn_helpcollations()

rdodson
Starting Member

1 Post

Posted - 2005-02-16 : 15:26:06

I am writing code to be used by SQL Server and Oracle. I would like
to use the same name for functions, without including the user qualifier. What I am learning is that SQL Server, apparently has
no way to allow a scaler function to be called without the user qualifier, is that a true statement? ie, there is no facility to point to a function with just a single name? similar to using a synonym in Oracle?

any input is appreciated... thank you, Ray Dodson.

quote:
Originally posted by robvolk

From Books Online:
quote:
Calling User-Defined Functions

When calling a scalar user-defined function, you must supply at least a two-part name:

SELECT *, MyUser.MyScalarFunction()
FROM MyTable

Table-valued functions can be called by using a one-part name:

SELECT *
FROM MyTableFunction()

However, when you call SQL Server built-in functions that return a table, you must add the prefix :: to the name of the function:

SELECT * FROM ::fn_helpcollations()

Ray Dodson

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-16 : 22:48:01

If you really wanted to do something like that, then you'd have to do it on the Oracle side, either with a synonym or by adding the user/schema name to the Oracle function.

It is nearly impossible to create perfectly compatible code between SQL Server and Oracle, and rarely worth the effort to even try. It will always perform badly on one if not both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources