May 14, 2002 at 1:54 pm
I am new to SQLServer and trying to call a UDF from a Select statement without the username prefix but in vain. How can I achive this?
select udf_get_name(id) from customer where id=10;
instead of
select cust.udf_get_name(id) from customer where id=10;
Thanks in advance.
May 14, 2002 at 2:38 pm
Sorry you cannot do this. From SQL 2000 BOL (updated)
quote:
Function InvocationScalar-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.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 3:59 pm
Thanks for your reply.
Could you tell me the difference between
scalar-valued and table-valued function?
May 14, 2002 at 9:04 pm
From MSDN:
User-defined functions always return a value. Depending on the type of value it returns, each user-defined function falls into one of three categories:
Scalar-valued function A user-defined function can return a scalar value such as an integer or a timestamp. If a function returns a scalar value, you can use it in a query anywhere you would use a column name.
Inline function If a user-defined function contains a single SELECT statement and that statement is updateable, then the tabular result returned by the function is also updateable. Such functions are called inline functions. When an inline function returns a table, you can use that function in the FROM clause of another query. For more information, see Using Something Else In Place of a Table.
Table-valued function If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updateable, then the tabular result returned by that function is not updateable. When a table-valued function returns a table, you can use that function in the FROM clause of another query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply