Calling the user defined function!

hai,

the problem is - I have created a userdefined function using SQL 2000

create function getfulldate (@.date varchar(10))
returns datetime
as
begin
declare @.getfulldate datetime
set @.getfulldate = dateadd (mi,55,@.date)

return @.getfulldate
end

and normally we call this in the SQL statements as

select *, dbo.getfulldate('2006-05-03') from emp

This works fine and what I need was, I need to invoke the user-defined function like

select *, getfulldate('2006-05-03') from emp that is, without using "dbo".

If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.

So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.

anyone guide me, thanks!

Hi,

User defined functions can not be called with schema's reference. You must include schema "dbo." reference on each function calls.

|||

however we are able to call without the reference of dbo, functions that return table except scalar values...that is Why?...so there must be some thing to known as how to call without dbo reference. also visit

http://microsoft.apress.com/asptodayarchive/73823/sql-user-defined-functions

waiting for reply

|||

Hi anandh2007,

You are right. We can call a table-value functin without schema name while we must specify the schema name when calling a scalar value function.

This problem is by design (something relating the sql-server compiler),see the online book, I've bolderd the important part

Function Invocation

Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement.

Scalar-valued functions must be invoked by using at least the two-part name of the function.

For more information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). Table-valued functions can be invoked where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. For more information, see Executing User-defined Functions (Database Engine).

Hope my suggestion helps