<table-valued function> is not a recognized built-in function name

Question

I am getting this error:

Msg 195, Level 15, State 10, Line 1
'fnParseName' is not a recognized built-in function name.

On this query:

SELECT  fnParseName(DOCTORFIRSTNAME+' ' +DOCTORLASTNAME) 
  FROM [PracticeandPhysician]

Here's the code for fnParseName

create FUNCTION [dbo].[fnParseName]
               (@FullName NVARCHAR(128))
RETURNS @FullNameParts TABLE  (FirstName  NVARCHAR(128),
                               Middle     NVARCHAR(128),
                               LastName   NVARCHAR(128))
AS
  BEGIN
    ... function body that populates @FullNameParts ...
    RETURN
  END

Why am I getting this error?

Answer 1

It's a table-valued function. So you probably meant:

SELECT p.DOCTORFISTNAME, p.DOCTORLASTNAME, t.FirstName, t.Middle, t.LastName
  FROM dbo.[PracticeandPhysician] AS p
  CROSS APPLY dbo.fnParseName(p.DOCTORFIRSTNAME + ' ' + p.DOCTORLASTNAME);

Note that you can't say:

SELECT dbo.TableValueFunction('foo');

Any more than you could say:

SELECT dbo.Table;
--or
SELECT dbo.View;

You can, however, say:

SELECT * FROM dbo.fnParseName('foo bar');
--or
SELECT FirstName, Middle, LastName FROM dbo.fnParseName('foo bar');

(Not that I have validated that your function does what you think, or does so efficiently.)

Please always use the dbo. prefix as others have suggested.

Answer 2

You always have to prefix SQL function calls with the schema name dbo. or the schema name for that function (dbo is the default schema).

SELECT dbo.fnParseName(--etc