Is there a way to use a function on a Microsoft SQL Server Query without using "dbo." before the function?

Question

Is there a way to call a User defined function without using "dbo." before the function name and parameters?

Using:

SELECT USERFUNCTION(PARAM1, PARAM2, PARAM3, PARAMN)

instead of:

SELECT dbo.USERFUNCTION(PARAM1, PARAM2, PARAM3, PARAMN)

Answer 1

This isn't possible for the SELECT syntax. BOL States: "Scalar-valued functions must be invoked by using at least the two-part name of the function"

This syntax works however.

CREATE FUNCTION USERFUNCTION
(@p INT)
RETURNS INT
AS
BEGIN
RETURN (2)
END

GO

DECLARE @rc INT

EXEC @rc = USERFUNCTION 1

SELECT @rc

It is best practice to always explicitly schema qualify objects you are referencing anyway though to avoid some overhead for resolving the schema (and avoid the possibility that the schema cannot be resolved implicitly or is resolved in a way that is undesired)

Answer 2

There are various ways to do this, if we take it that you have a negative reaction to seeing "dbo.".

In SQL Server 2000, there is a way to turn UDFs into system functions by toggling a bit. This "feature" has been removed from SQL Server 2005 onwards, so I won't go into detail unless you really are still using 2000.

You can use OPENQUERY with PROC syntax similar to what Martin has shown.

You can turn the Scalar function into a Table Valued Function, either by rewriting it, or by wrapping it in a TVF. The syntax changes however, so

select dbo.udf(a,b) from c

--becomes
select d
from c
cross apply tvf(a,b) e(d)  -- look, no "dbo"!

But none of the above looks simpler than just tacking a simple "dbo." prefix to the function name, so why would you do it?