SQL Server创建表值函数报BEGIN、变量未声明等语法错误求解

你编写的保单缴费周期查询函数触发的三个语法错误，核心原因是混淆了SQL Server两类表值函数的语法规则：

• 内联表值函数仅支持RETURNS TABLE AS RETURN(单条SELECT查询)的结构，不允许写BEGIN/END块、声明变量、执行多步逻辑，你在RETURN后的括号内直接写BEGIN、声明变量的写法，直接触发了BEGIN附近语法错误的报错，同时导致函数入参无法被解析，触发必须声明标量变量@policybillid的报错，最后括号和代码块结构不匹配，触发末尾右括号语法错误。
• 如果要实现多步逻辑、变量声明、条件判断，必须使用多语句表值函数，显式定义返回值的表结构，逻辑全部写在AS BEGIN ... END块内，最后通过RETURN返回结果。

原代码除了结构错误外，还存在逻辑缺漏：入参@companyID未被使用、变量赋值后没有作为结果返回、取到期日的排序规则不明确。

修正后可直接运行的代码 #

USE [defaultDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION [dbo].[fn_Payment_GetPaymentCoutesByPaymentID]( 
    @policybillid BIGINT, 
    @companyID BIGINT
) 
-- 显式定义返回表结构
RETURNS @Result TABLE(PaymentCycle VARCHAR(20))
AS
BEGIN
    DECLARE @expiresDates TABLE(expiredOn DATE)
    DECLARE @diffMonth INT
    -- 默认值设为Cash，覆盖缴费记录不足2条的场景
    DECLARE @quota VARCHAR(20) = 'Cash'

    -- 取该保单最早的两期到期日
    INSERT INTO @expiresDates
    SELECT TOP(2) expiredOn 
    FROM PolicyPaymentPlan 
    LEFT JOIN PolicyPaymentPlanFee 
        ON PolicyPaymentPlan.paymentPlanID = PolicyPaymentPlanFee.paymentPlanID
    WHERE PolicyPaymentPlan.policyBillID = @policybillid
    ORDER BY expiredOn ASC

    -- 存在两期及以上记录时计算月差
    IF (SELECT COUNT(*) FROM @expiresDates) > 1
    BEGIN
        SET @diffMonth = DATEDIFF(
            MONTH,
            (SELECT TOP 1 expiredOn FROM @expiresDates ORDER BY expiredOn ASC),
            (SELECT TOP 1 expiredOn FROM @expiresDates ORDER BY expiredOn DESC)
        )
        SET @quota = CASE
            WHEN @diffMonth = 1 THEN 'Monthly'
            WHEN @diffMonth = 2 THEN 'bimonthly'
            WHEN @diffMonth = 3 THEN 'trimonthly'
            WHEN @diffMonth = 4 THEN 'Four-Monthly'
            WHEN @diffMonth = 6 THEN 'biannual'
            ELSE 'Cash'
        END
    END

    -- 写入返回结果
    INSERT INTO @Result(PaymentCycle) VALUES (@quota)
    RETURN
END
GO

可选优化 #

如果追求更高的查询性能，可以把上述逻辑改写为单SELECT的内联表值函数，避免多语句表值函数的性能开销，示例写法：

CREATE OR ALTER FUNCTION [dbo].[fn_Payment_GetPaymentCyclesByPaymentID]( 
    @policybillid BIGINT
) 
RETURNS TABLE AS
RETURN(
    WITH DateRank AS (
        SELECT 
            expiredOn,
            ROW_NUMBER() OVER(ORDER BY expiredOn ASC) AS rn,
            COUNT(1) OVER() AS totalCount
        FROM (
            SELECT DISTINCT TOP(2) expiredOn 
            FROM PolicyPaymentPlan 
            LEFT JOIN PolicyPaymentPlanFee 
                ON PolicyPaymentPlan.paymentPlanID = PolicyPaymentPlanFee.paymentPlanID
            WHERE PolicyPaymentPlan.policyBillID = @policybillid
            ORDER BY expiredOn ASC
        ) t
    )
    SELECT 
        CASE
            WHEN totalCount < 2 THEN 'Cash'
            WHEN DATEDIFF(MONTH, MIN(CASE WHEN rn=1 THEN expiredOn END), MIN(CASE WHEN rn=2 THEN expiredOn END)) =1 THEN 'Monthly'
            WHEN DATEDIFF(MONTH, MIN(CASE WHEN rn=1 THEN expiredOn END), MIN(CASE WHEN rn=2 THEN expiredOn END)) =2 THEN 'bimonthly'
            WHEN DATEDIFF(MONTH, MIN(CASE WHEN rn=1 THEN expiredOn END), MIN(CASE WHEN rn=2 THEN expiredOn END)) =3 THEN 'trimonthly'
            WHEN DATEDIFF(MONTH, MIN(CASE WHEN rn=1 THEN expiredOn END), MIN(CASE WHEN rn=2 THEN expiredOn END)) =4 THEN 'Four-Monthly'
            WHEN DATEDIFF(MONTH, MIN(CASE WHEN rn=1 THEN expiredOn END), MIN(CASE WHEN rn=2 THEN expiredOn END)) =6 THEN 'biannual'
            ELSE 'Cash'
        END AS PaymentCycle
    FROM DateRank
)
GO

注：原函数名中的Coutes应为拼写错误，优化版本中修正为Cycles，可根据实际命名规范调整。

内容的提问来源于stack exchange，提问作者Cristian Javier Blanco García