创建无参SQL表值函数时声明变量报错，是否需改用BEGIN块？

是的，你必须用BEGIN/END块来包含变量声明和逻辑，但要先调整函数类型——你当前写的是内联表值函数，这种函数的RETURN()里只能直接放SELECT语句，不允许包含变量声明、赋值等逻辑。要实现带变量的逻辑，需要改成多语句表值函数，具体调整如下：

修正后的完整代码 #

CREATE FUNCTION dbo.DistanceSales() 
RETURNS @ResultTable TABLE (
    AddressID INT,
    AddressLine NVARCHAR(255),
    City NVARCHAR(100),
    Province NVARCHAR(100),
    Country NVARCHAR(100),
    Latitude FLOAT,
    Longitude FLOAT,
    Distance NVARCHAR(20),
    GroupDistance NVARCHAR(10)
)
AS
BEGIN
    DECLARE @Location GEOGRAPHY;
    SET @Location = GEOGRAPHY::Point(87.63945, -187.12826, 4326);

    INSERT INTO @ResultTable
    SELECT 
        DISTINCT A.AddressID,
        A.AddressLine,
        A.City,
        P.Name AS Province,
        C.Name AS Country,
        A.SpatialLocation.Lat AS Latitude,
        A.SpatialLocation.Long AS Longitude,
        FORMAT((@Location.STDistance(A.SpatialLocation)/1000), 'N2') AS Distance,
        CASE
            WHEN (@Location.STDistance(A.SpatialLocation)/1000) <= 10 THEN '1'
            WHEN (@Location.STDistance(A.SpatialLocation)/1000) > 10 AND (@Location.STDistance(A.SpatialLocation)/1000) <= 50 THEN '2'
            WHEN (@Location.STDistance(A.SpatialLocation)/1000) > 50 AND (@Location.STDistance(A.SpatialLocation)/1000) <= 300 THEN '3'
            WHEN (@Location.STDistance(A.SpatialLocation)/1000) > 300 AND (@Location.STDistance(A.SpatialLocation)/1000) <= 700 THEN '4'
            WHEN (@Location.STDistance(A.SpatialLocation)/1000) > 700 AND (@Location.STDistance(A.SpatialLocation)/1000) <= 1000 THEN '5'
            ELSE 'MAX'
        END AS GroupDistance
    FROM Sales S
    INNER JOIN Address A ON A.AddressID = S.ShipID
    INNER JOIN Province P ON P.ProvinceID = A.ProvinceID
    INNER JOIN Country C ON C.CountryID = P.CountryCode;

    RETURN;
END
GO

SELECT * FROM DistanceSales();

关键调整说明 #

• 函数类型转换：从内联表值函数改为多语句表值函数，需要先定义返回表@ResultTable的字段结构
• 逻辑包裹：用BEGIN/END块包含变量声明、赋值和数据插入逻辑
• 修复笔误：原CASE语句里的@HQLocation是拼写错误，统一改为@Location
• 性能优化：去掉A.SpatialLocation.ToString()，直接传入地理类型参数给STDistance，避免不必要的类型转换

内容的提问来源于stack exchange，提问作者ProgrammingGirly