Why calling a user defined function need the owner name when calling a stored procedure doesn't ? Please help!
views:
62answers:
3
+1
Q:
Why calling a user defined function needs the owner name when calling a stored procedure doesn't ?
Yeah, you're right Ian. I've just tested and confirm what you said! So from this, can we say that "there's no system table-value function" ?
Nam Gi VU
2010-03-07 05:54:37
+3
A:
I assume you mean the schema name? Owner is the user that created it.
It distinguishes the function from a built-in function. "System" stored procedures and functions live in the master database (so it can be searched), while built-in functions (things like DATEADD) reside in the database engine themselves.
I guess it's more difficult when you specify SELECT MyFunction() for the database engine to work out whether you mean a function that lives in a database, or a built-in function.
Andy Shellam
2010-03-05 11:56:05
Thanks Andy!Yes I mean the schema name.Well, what you said helps but I do not totally agree with you. Why don't we need to distinguish user's stored procedure vs built-in stored procedure?
Nam Gi VU
2010-03-07 05:40:48
Because stored procedures only ever live in the database, they're never built-in, so the engine can search both the master database and the current database. That's why you should try not to name your procedure sp_something. If SQL sees sp_ at the start of your procedure it searches the master database first.
Andy Shellam
2010-03-07 09:04:21
Thanks Andy. I understand it now.As Ian said below, we only need the schema for scalar functions. For table-value functions, we don't need ones. I would like to hear your thinking about this Andy. Please discuss with me.
Nam Gi VU
2010-03-07 10:27:32
Yeah that's probably right, because as far as I'm aware there are no built-in functions that return a table - they all operate on, and return, scalar values.
Andy Shellam
2010-03-07 21:19:48
A:
After discussing with Andy above, I answer my own question here: Stored procedures are stored in the current database of the connection when created. In the mean time, functions are stored two locations: in the database engine (the scalar-value built-in ones) and in the current database of the connection (the user-defined ones). So the when calling the user-defined scalar-value functions, we need the schema name prefix to distinguish them vs the built-in ones. We don't need this prefix for the other types of functions and for the stored procedures.
Nam Gi VU
2010-03-08 03:31:26