468,242 Members | 1,504 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

default schema resolution for user-defined functions?

aj
SQL Server 2005 64-bit 9.00.3042 SP2

When I map a database user to a login, I can specify a default schema
for that user. After that, any SQL from that user w/o an explicit
schema will be resolved to that default schema.

I would like for this to happen in regards to user-defined functions
(in this case, a scalar-valued function). However, it looks as if
I must always explicitly specify the schema when using the function.

In other words: I have a function foo() and a table bar in schema
myschema. My default schema is myschema.

This works:
select blah from bar

And this works:
select blah,myschema.foo() from bar

But this apparently won't work:
select blah,foo() from bar

Why?

Is default schema not considered in user-defined function name
resolution?

TIA

aj
Oct 29 '08 #1
2 3572
Scalar-valued functions must be invoked by using at least two part name
notation. Table-valued functions can be invoked only by name.

--
Plamen Ratchev
http://www.SQLStudio.com
Oct 29 '08 #2
aj (ro****@mcdonalds.com) writes:
But this apparently won't work:
select blah,foo() from bar

Why?

Is default schema not considered in user-defined function name
resolution?
Correct. You must always specify the schema with scalar UDFs. This
permits Microsoft to add new scalar system functions in future releases
of SQL Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Oct 29 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by inquirydog | last post: by
55 posts views Thread by Haines Brown | last post: by
4 posts views Thread by Hemant Shah | last post: by
7 posts views Thread by greatcanyon | last post: by
1 post views Thread by Nalaka | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.