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

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

Using:

  • how to put words into a variables and check if it matches the data in database
  • INSERT from ASP.NET to MS Access
  • Querying the Result set of a Previous Query
  • Using SQL Server MERGE command with same source & target table
  • Multiple rows, with multiple values, how to get higher value for same row ID
  • Case statement not correctly matching expected values
  • SELECT USERFUNCTION(PARAM1, PARAM2, PARAM3, PARAMN)
    

    instead of:

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

    4 Solutions collect form web for “Is there a way to use a function on a Microsoft SQL Server Query without using “dbo.” before the function?”

    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 schema qualify objects you are referencing anyway though to avoid some overhead for resolving the schema (and I think for plan cache reasons also)

    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?

    See this question for an explanation:

    lu.[TableName] instead of dbo.[TableName]?

    The wrong assumption is:
    dbo is the schema so if you create the userfunction in your own schema and run the select statement in your own schema than you’ll be fine.

    The correct answer is:
    You cann’t get that to work for userfunctions directly in a select statement… too bad…

    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 .

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.