使用命名参数调用内联 TVF，正确的语法是什么? [英] Calling an in-line TVF with named parameters, what is the proper syntax?

问题描述

我尝试使用位置参数调用内嵌 TVF 并且工作正常:

I tried calling an in-line TVF with positional parameters and it works fine:

SELECT MyTable.Col1,
       (SELECT TvfColumn FROM ufnGetData(MyTable.Col1, MyTable.Col2)),
       MyTable.Col2
FROM MyTable

然后我尝试将参数命名为 TVF，但它没有解析:

Then I tried to name the parameters to the TVF and it does not parse:

SELECT MyTable.Col1,
       (SELECT TvfColumn FROM ufnGetData(@Param1=MyTable.Col1, @Param2=MyTable.Col2)),
       MyTable.Col2
FROM MyTable

函数看起来像:

CREATE FUNCTION dbo.ufnGetData
(   
    @Param1 INT,
    @Param2 INT
)
RETURNS TABLE 
AS
RETURN 
(
SELECT 
       blah blah blah AS TvfColumn
)
GO

给出类似于以下内容的错误:

Giving an error similar to:

Msg 137, Level 15, State 2, Line 23 必须声明标量变量@Param2".

Msg 137, Level 15, State 2, Line 23 Must declare the scalar variable "@Param2".

消息 102，级别 15，状态 1，第 24 行 ',' 附近的语法不正确.

Msg 102, Level 15, State 1, Line 24 Incorrect syntax near ','.

我做错了什么?

推荐答案

不能将表值用户定义函数视为存储过程.调用它们时不要使用命名参数，它是基于位置的.因此，换句话说，您尝试做的事情是不可能的.

Table-Valued User-Defined Functions can't be treated like a Stored Procedure. You don't use named parameters while calling them, it is position-based. So in other words, what you are trying to do isn't possible.

您是否有理由使用命名参数?您需要定义所有参数，因此没有您通常会在存储过程中看到的真正好处.

Is there a reason why you want to use named parameters? You need to have all parameters defined, so there is no real benefit that you would normally see with stored procedures.

这篇关于使用命名参数调用内联 TVF，正确的语法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！