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

[LineVoltageHalogen]

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

[Erland Sommarskog]

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