Calling UDF without dbo.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 2000 Forums

SQL Server Administration (2000)

Calling UDF without dbo.function

Author

Topic

ivacacela
Starting Member

10 Posts

Posted - 2006-06-28 : 18:09:03

Hi,

I was wondering if there is any way to define a UDF as public or independant of the database in order to call

select udf1(a,b) from table1 instead of (without the "dbo.")

select dbo.udf1(a,b) from table1

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-28 : 18:38:54

You must specify the object owner when calling functions.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 19:21:12

The owner is required for scaler functions, but is not required for table valued functions. Example with each type:


select
	[Week Start Date] = dbo.F_START_OF_WEEK(a.[DATE],1)
from
	F_TABLE_DATE('20060621','20060630') a

CODO ERGO SUM

ivacacela
Starting Member

10 Posts

Posted - 2006-06-28 : 19:30:34

Thanks for your answer.
In that case is not possible to avoid use of 'dbo.' prefix.

Well I have to find out other implementation.

Best Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-28 : 19:31:30

Why do you need to find another implementation? Why can't your software prefix it with the object owner?

Tara Kizer
aka tduggan

ivacacela
Starting Member

10 Posts

Posted - 2006-06-28 : 20:16:32

Thanks again Tara.
If you have 2 minutes maybe you can have a better idea. I'm updating an application based on Oracle to include support for other databases (SQL Server, MySQL, etc.).
I have many SQL calls using concat() function like:
select concat(firstname, lastname) as fullname from personal;.
I'm trying to avoid include an IF or CASE like

CASE Database = 'SQL Server'
select firstname+lastname as fullname from personal

CASE Database = 'ORACLE'
select concat(firstname, lastname) as fullname from personal

The first idea was to create a UDF concat() in SQL Server to provide the same functionality . If I dont find other better option I could use a parametric prefix like

CASE Database = 'SQL Server'
prefix = 'dbo.'
CASE Database = 'ORACLE'
prefix = ''

I think it works if I dont get other option.

Regards

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 20:25:53

That will likely be the least of your issues.

I really don't see how you are going to avoid having different sql code for each database you plan to support. Not to mention differences in datatypes, how you connect, security, and about a hundred other things.

CODO ERGO SUM

ivacacela
Starting Member

10 Posts

Posted - 2006-06-28 : 20:41:13

Thanks Michael for your code.
The most of the work is almost done. I have many programs to convert data types, etc., and I'm using ADO to connect to databases and is working fine. For now we are only supporting 3 or 4 databases. For now the last problem is that not all the databases have all same pre defined functions. Fortunately the original application has only a few (4 or 5) functions. But I have special problem with concat in SQL Server I think is the only one (of our supported DB) without this function.

Regards.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources