February 19, 2002 at 6:03 am
Hi,
I've recently starting exploring the use of UDFs. I seem to have found what I would consider is a bug. If not please let me know where I am going wrong.
Heres a stub function:
CREATE FUNCTION dbo.udfTEST (@client char(5))
RETURNS int AS
BEGIN
RETURN 1
END
Now my problem lies with executing this function. From the help I should be able to run the following:
SELECT udfTEST('sdfs')
but I just get:
'udfTEST' is not a recognized function name.
This however works:
SELECT dbo.udfTEST('sdfs')
THe user I am running under is dbo.
Any ideas?? as I dont really want to have to reference all the objects with the owner.
thanks
Craig
February 19, 2002 at 7:05 am
From BOL:
Calling User-Defined Functions
When calling a scalar user-defined function, you must supply at least a two-part name:
SELECT *, MyUser.MyScalarFunction()
FROM MyTable
Table-valued functions can be called by using a one-part name:
SELECT *
FROM MyTableFunction()
However, when you call SQL Server built-in functions that return a table, you must add the prefix :: to the name of the function:
SELECT * FROM ::fn_helpcollations()
Andy
February 19, 2002 at 7:15 am
But why then does the following work:
SELECT dbo.udfTEST('sdfs')
Also why do you have to prefix the object with the owner? You dont need to do this with any other SQL objects? Is there some reason for this?
February 19, 2002 at 7:48 am
These should answer your question. They are from SQL Books Online. Statement 1 in Function Invocation and statement 3 in Permissions apply to your question.
Function Invocation
Scalar-valued functions may be invoked where scalar expressions are used, including computed columns and CHECK constraint definitions. When invoking scalar-valued functions, at minimum use the two-part name of the function.
[database_name.]owner_name.function_name ([argument_expr][,...])
If a user-defined function is used to define a computed column, the function's deterministic quality also defines whether an index may be created on that computed column. An index can be created on a computed column that uses a function only if the function is deterministic. A function is deterministic if it always returns the same value, given the same input.
Table-valued functions can be invoked using a single part name.
[database_name.][owner_name.]function_name ([argument_expr][,...])
System table functions that are included in Microsoft® SQL Server™ 2000 need to be invoked using a '::' prefix before the function name.
SELECT *
FROM ::fn_helpcollations()
Transact-SQL errors that cause a statement to be stopped and then continued with the next statement in a stored procedure are treated differently inside a function. In functions, such errors will cause the function execution to be stopped. This in turn will cause the statement that invoked the function to be stopped.
Permissions
Users should have the CREATE FUNCTION permission to execute the CREATE FUNCTION statement.
CREATE FUNCTION permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of sysadmin and db_owner can grant CREATE FUNCTION permissions to other logins by using the GRANT statement.
Owners of functions have EXECUTE permission on their functions. Other users do not have EXECUTE permissions unless EXECUTE permissions on the specific function are granted to them.
In order to create or alter tables with references to user-defined functions in the CONSTRAINT, DEFAULT clauses, or computed column definition, the user must also have REFERENCES permission to the functions.
February 19, 2002 at 8:08 am
quote:
But why then does the following work:SELECT dbo.udfTEST('sdfs')
Just a small bit to add to what Antares posted. The example you've given is a two part name. dbo is the owner of the User Defined Function. So it works because it fits the requirement of two part name.
As for why a two part name is required, I'm not sure. A quick glance through BOL didn't reveal anything. Microsoft has to have a reason for requiring the two part name for a scalar UDF.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply