Is there a way to use a function on a Microsoft SQ

问题:

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)

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

回答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?

回答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 .