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?

it's a table valued function if that helps – Alex Gordon Mar 05 '12 at 23:28 — Alex Gordon, Mar 05 '12 at 23:28

Aaron Bertrand · Accepted Answer · 2020-05-13T02:03:39.270

20

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.

edited May 13 '20 at 02:03

answered Mar 05 '12 at 23:45

Aaron Bertrand

272,866
37
466
490

Didn't I show that in the very first sample, using `CROSS APPLY`? It should output columns from every input row in `dbo.[PracticeandPhysician]`. – Aaron Bertrand Mar 06 '12 at 03:10
"CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function." From the MSDN documentation: http://msdn.microsoft.com/en-us/library/ms175156.aspx – Aaron Bertrand Mar 06 '12 at 04:04
plus one for 'always use the dbo prefix' – Hakan Fıstık Oct 24 '16 at 13:28

score 12 · Answer 2 · edited Mar 05 '12 at 23:23

12

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

edited Mar 05 '12 at 23:23

DaveShaw

52,123
16
112
141

answered Mar 05 '12 at 23:20

Peter Wishart

11,600
1
26
45

Msg 4121, Level 16, State 1, Line 2 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnParseName", or the name is ambiguous. – Alex Gordon Mar 05 '12 at 23:23
@MattGibson nope definitely not, i can send u snapshot if u want – Alex Gordon Mar 05 '12 at 23:27
@MattGibson it's a table valued function if that helps – Alex Gordon Mar 05 '12 at 23:27
Yes check you didn't accidentally create the function on master.. its easily done when opening a new connection to run a sql batch. – Peter Wishart Mar 05 '12 at 23:28
If you run "sp_help 'fnParseName'" what does it return? – Peter Wishart Mar 05 '12 at 23:30
@PeterWishart yes i agree with you but i confirmed that i created it on the correct db – Alex Gordon Mar 05 '12 at 23:35
2

@Peter it's a table-valued function. The prefix is important but the real problem is you can't reference a table-valued function like an expression (though you can with a scalar UDF). – Aaron Bertrand Mar 05 '12 at 23:46
Yes, I was wondering about that, @Aaron. Couldn't quite see what would be returned, given the syntax, even if it would work. – Matt Gibson Mar 05 '12 at 23:48

user1656779 · Answer 3 · 2015-01-05T04:27:47.570

The problem you have is similar to what I encountered too. Scalar function and Table inline functions are quite different in terms of implementation. See below for the diiferent

 Create function udfCountry
 (
@CountryName varchar(50)
 )
 returns varchar(2)
 as 
 BEGIN
 Declare @CountryID varchar(2),
    @Result varchar(2)

 Select @CountryID = Country from 
dbo.GeoIPCountryNames where CountryName = @CountryName

set @Result = isNull(@CountryID, 'NA')
if @Result = 'NA'
set @Result = 'SD'
return @Result
End

//Implementation

select dbo.[udfCountry]('Nigeria')

// sample result

NG

// Inline table function sample

 Create FUNCTION  ConditionEvaluation
 (
  @CountrySearch varchar(50)
 )
 returns @CountryTable table 
(
Country varchar(2),
CountryName varchar(50)
 )
 as 

 Begin

 Insert into @CountryTable(Country, CountryName)
 Select Country, CountryName from GeoIPCountryNames
 where Country like '%'+@CountrySearch+'%'
 return 
 end

//Implementation sample

 Declare @CountrySearch varchar(50)
 set @CountrySearch='a'
 select * from  ConditionEvaluation(@CountrySearch)

the parttern of implementating scalar is quite different inline table. I hope this helps

score 1 · Answer 4 · edited Jan 28 '16 at 12:29

1

If you want to assign the value returned by tfn in a variable of stored procedure, you can do it this way:

select  @my_local_variable_in_procedure = column_name_returned_from_tfn from dbo.my_inline_tfn (@tfn_parameter)

edited Jan 28 '16 at 12:29

Michael

1,453
3
20
28

answered Jan 28 '16 at 11:39

Sandeep Gaadhe

367
4
4

score 1 · Answer 5 · answered Mar 05 '12 at 23:22

1

UDFs/Functions need to be prefixed with the schema name (most likely "dbo"). Change the call to

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

answered Mar 05 '12 at 23:22

JackAce

1,407
15
32

Msg 4121, Level 16, State 1, Line 2 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnParseName", or the name is ambiguous. – Alex Gordon Mar 05 '12 at 23:23
The schema prefix is not the issue here (while it will still be required, there is a more fundamental syntax issue). – Aaron Bertrand Mar 05 '12 at 23:54

is not a recognized built-in function name

5 Answers5

Linked