Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

SELECT USERFUNCTION(PARAM1, PARAM2, PARAM3, PARAMN)

instead of:

SELECT dbo.USERFUNCTION(PARAM1, PARAM2, PARAM3, PARAMN)
like image 784
alexzm1 Avatar asked Mar 05 '11 21:03

alexzm1


People also ask

Can we use function in SQL query?

Functions can be used anywhere in SQL, like AVG, COUNT, SUM, MIN, DATE and so on with select statements. Functions compile every time. Functions must return a value or result. Functions only work with input parameters.


2 Answers

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)

like image 104
Martin Smith Avatar answered Sep 29 '22 04:09

Martin Smith


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?

like image 25
RichardTheKiwi Avatar answered Sep 29 '22 05:09

RichardTheKiwi