CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60711

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

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?

Upvotes: 16

Views: 35095

Answers (5)

anon
anon

Reputation:

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.

Upvotes: 21

Sandeep Gaadhe
Sandeep Gaadhe

Reputation: 377

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)

Upvotes: 1

user1656779
user1656779

Reputation: 241

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

Upvotes: 1

Peter Wishart
Peter Wishart

Reputation: 12270

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

Upvotes: 12

JackAce
JackAce

Reputation: 1405

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

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

Upvotes: 1

Related Questions