Sql server用户定义函数的开始句子删除错误

With this user defined function I am receiving error 'Incorrect syntax near 'BEGIN'. If I drop out BEGIN, then the warning is gone. Why I can not use BEGIN in this case?

使用这个用户定义的函数，我在“BEGIN”附近接收到错误的“不正确的语法”。如果我退出start，那么警告就消失了。为什么在这种情况下我不能开始使用?

ALTER FUNCTION dbo.fnMyFunc
(
@MyType int
)
RETURNS TABLE
AS
BEGIN
RETURN SELECT tbl.[UID] FROM 
(
SELECT [UP].[UID], [UP].[ADDITIONAL_IDENTIFIER], MAX(VALID_FROM) [VALID_FROM]
FROM [CM].[USER_PROPS] [UP]
GROUP BY [UP].[UID], [UP].[ADDITIONAL_IDENTIFIER]
HAVING [UP].[ADDITIONAL_IDENTIFIER] = @MyType
) tbl
END

2 个解决方案

#1

For Inline Table-Valued Function, this is the MS SQL syntax.

对于内联表值函数，这是MS SQL语法。

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

For Multi-statement Table-Valued Function, this is the MS SQL syntax.

对于多语句表值函数，这是MS SQL语法。

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    RETURN 
END
GO

In your case, you are using Inline Table-Valued Function, so that Begin is not allowed.

在您的示例中，您正在使用内联表值函数，因此不允许使用Begin。

#2

An inline table-valued function can only have one statement. The BEGIN...END syntax is simply not defined here. There is never a need for it since you can't use more than one statement anyway.

内联表值函数只能有一条语句。开始……这里没有定义结束语法。因为无论如何你不能使用多个语句，所以没有必要使用它。

The reason for this restriction is that SQL Server needs to be able to inline the function body into its calling query. This is possible in an efficient and simple way if there is only one SELECT statement and nothing else.

这种限制的原因是SQL Server需要能够将函数体内联到它的调用查询中。如果只有一个SELECT语句而没有其他语句，那么这可以以一种高效和简单的方式实现。

#1