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?
SELECT USERFUNCTION(PARAM1, PARAM2, PARAM3, PARAMN)
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…
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.
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 .