469,106 Members | 2,314 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

why does a user function require dbo.* and a stored proc does not?

I have many stored procs in my database and I can call them just by
their name uspMyProc with success always. However, I just created a
user function ufnMyFunction as the same user that I created my procs
but when I call ufnMyFunction it fails unless I preface it with dbo. .
How come the stored proc does not require this but the stored function
does?

TFD

Jul 23 '05 #1
1 1951
LineVoltageHalogen (tr****************@yahoo.com) writes:
I have many stored procs in my database and I can call them just by
their name uspMyProc with success always. However, I just created a
user function ufnMyFunction as the same user that I created my procs
but when I call ufnMyFunction it fails unless I preface it with dbo. .
How come the stored proc does not require this but the stored function
does?


The reason that you must specify schema(*) when you invoke a UDF is
mainly a syntactical one. If you could say just "SELECT MyUDF(a) FROM tbl",
and Microsoft in next version would introduce a new system function
with that name, they would have broken your code.

To avoid this, Microsoft needed to find a syntactic way to distinguish
between a user-defined function and a system function. They opted to
require schema to be mandatory.

(*) or owner. In SQL 2000 schema = owner. Not so in SQL 2005!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Newbie | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.