I have migrated a database from oracle, and now have a few Scalar-valued Functions.
However, when I call them, I get an error saying:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.chk_mgr", or the name is ambiguous.
I'm calling it like this:
SELECT dbo.chk_mgr('asdf')
What am I doing wrong?
Are you sure it's not a Table-Valued Function
?
The reason I ask:
CREATE FUNCTION dbo.chk_mgr(@mgr VARCHAR(50))
RETURNS @mgr_table TABLE (mgr_name VARCHAR(50))
AS
BEGIN
INSERT @mgr_table (mgr_name) VALUES ('pointy haired boss')
RETURN
END
GO
SELECT dbo.chk_mgr('asdf')
GO
Result:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function
or aggregate "dbo.chk_mgr", or the name is ambiguous.
However...
SELECT * FROM dbo.chk_mgr('asdf')
mgr_name
------------------
pointy haired boss