Call function without db prefix

[kindred]

Is there a way to call a function without prefixing it with the database and/or owner name?

Example... fn_myfunction under the Sample Database

Currently I call it as followed...
Update mytable Set myfield = Sample.dbo.fn_myfunction(123)

I'd like to call it like this...
Update mytable Set myfield = fn_myfunction(123)


Thanks for the help,
Stephen

 

[mrdenny]

You need to specify the owner. The database is not needed unless you are calling if from another database.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLBill]

You don't need the prefix. The ONLY times the prefixes are needed is:

1. the owner is different, then you need to include the owner prefix

2. the database is different, then you need to include the database prefix (if the owner is the same you can use the double-dot notation - dbname..table)

3. the server is different, then you need to include the server prefix.

You are running the function in the same database as the query, you don't need the database prefix. Is the owner the same on both the database and the function? If so, you don't need that prefix.

-SQLBill

 

[SQLSister]

Bill, you are only correct if the function returns a table not a value. From BOL:

When calling a scalar user-defined function, you must supply at least a two-part name:

SELECT *, MyUser.MyScalarFunction()
FROM MyTable

Table-valued functions can be called by using a one-part name:

SELECT *
FROM MyTableFunction()

Questions about posting. See faq183-874

 

[kindred]

It's odd that you can't just call the function for a scalor return value. I would of thought the connection-string in Ole DB (or ODBC) would dictate the user for the calling function.

 

[SQLSister]

Just one of those quirky things. I imagine two differnt programmers built the two kinds of functions and didn't coordinate very well.

Questions about posting. See faq183-874

 

[SQLBill]

Thank you SQLSister for pointing that out...don't know how I missed it (I even looked through BOL before posting).

-SQLBill

 

[SQLSister]

I found this one out the hard way. Usually I use table functions. The first time I used a scalar function I spent hours trying to figure out why it wouldn't work.

Questions about posting. See faq183-874