June 13, 2005 at 9:21 pm
I am confused of how to call a UDF. I have 2 UDFs, fnStringFunction and fnTableFunction, which return a varchar and a table respectively. Both have dbo as the owner. However, I have to add dbo. before fnStringFunction but not fnTableFunction. e.g.
select * from @fnTableFunction
declare @myString varchar(100)
set @myString = dbo.fnStringFunction
-- but this will fail
set @myString = fnStringFunction
go
Why? What are the rules?
June 13, 2005 at 11:14 pm
Don't know all the exceptions, but as far as I know you must always specify the owner when referencing a function (and is a best practice to always do it) and I think you have to use the () too. But this is something I do to make sure I can diferenciate the functions from the views and tables.
June 15, 2005 at 3:37 am
Please refer to SQL Server Books Online.
***********************************************
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][,...])
***********************************************
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.
****************************************
Hope this will help
Leo
June 15, 2005 at 11:31 pm
select * from @fnTableFunction -- I don't think this is a function? more like a table variable.
June 16, 2005 at 12:01 am
Whoop, it's a typo. But
select * from fnTableFunction()
does work.
Nevertheless, accroding to the feedback I received, I will put the owner in front of all function anyway.
Thanks to all who gives me the helpful feedback.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply