SQL <表值函数> 不是可识别的内置函数名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9575622/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
<table-valued function> is not a recognized built-in function name
提问by l--''''''---------''''''''''''
I am getting this error:
我收到此错误：
Msg 195, Level 15, State 10, Line 1
'fnParseName' is not a recognized built-in function name.
消息 195，级别 15，状态 10，第 1 行
“fnParseName”不是可识别的内置函数名称。
On this query:
在此查询中：
SELECT fnParseName(DOCTORFIRSTNAME+' ' +DOCTORLASTNAME)
FROM [PracticeandPhysician]
Here's the code for fnParseName
这是 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?
为什么我收到这个错误？
回答by Aaron Bertrand
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.prefixas others have suggested.
回答by Peter Wishart
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).
您始终必须使用架构名称dbo.或该函数的架构名称作为SQL 函数调用的前缀（dbo 是默认架构）。
SELECT dbo.fnParseName(--etc
回答by HymanAce
UDFs/Functions need to be prefixed with the schema name (most likely "dbo"). Change the call to
UDF/函数需要以架构名称（最有可能是“dbo”）作为前缀。将呼叫更改为
SELECT
dbo.fnParseName(DOCTORFIRSTNAME + ' ' + DOCTORLASTNAME)
FROM
[PracticeandPhysician]
回答by user1656779
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
实现标量的部分是完全不同的内联表。我希望这有帮助
回答by Sandeep Gaadhe
If you want to assign the value returned by tfn in a variable of stored procedure, you can do it this way:
如果要在存储过程的变量中分配 tfn 返回的值，可以这样做：
select @my_local_variable_in_procedure = column_name_returned_from_tfn from dbo.my_inline_tfn (@tfn_parameter)
