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?

Answer 3

Yes Possible, Actually when function returning scalar value you must call with schema name like dbo.yourfunction , If you don't want to call function without schema name you should create function as follows.

Sample Code:

CREATE FUNCTION [dbo].[FN_MAPCOUNT] 
(

    @countValue int
)
RETURNS @TEMPTABLE Table(cntValue int)
as
begin
  DECLARE @countValueint
  @countValue= select count(*) from mappings;

   INSERT @TEMPTABLE (cntValue) VALUES (@countValue) 
    RETURN
end
Go

select * from FN_MAPCOUNT(1); 

The reason is you are returning the value as table .