具有声明变量的SQL函数

Question

我必须在SQL中创建一个函数，该函数不接受任何参数，但返回一个包含多个信息的表。然而，在我的函数中，我必须包含一个我声明的包含坐标的变量。因此，我选择了不同的信息来包含在表中，但当我包含声明变量时，我的函数就不起作用了。

CREATE FUNCTION dbo.DistanceSales() RETURNS TABLE
AS
RETURN(
DECLARE @Location GEOGRAPHY;
SET @Location=GEOGRAPHY::Point(87.63945,-187.12826,4326)
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.ToString())/1000),'N2') AS Distance,
CASE
WHEN (@Location.STDistance(A.SpatialLocation.ToString())/1000)  <= 10 THEN '1'
WHEN (@Location.STDistance(A.SpatialLocation.ToString())/1000) > 10 AND (@HQLocation.STDistance(A.SpatialLocation.ToString())/1000) <=50 THEN '2'
WHEN (@Location.STDistance(A.SpatialLocation.ToString())/1000) > 50 AND (@Location.STDistance(A.SpatialLocation.ToString())/1000) <=300 THEN '3'
WHEN (@Location.STDistance(A.SpatialLocation.ToString())/1000) > 300 AND (@Location.STDistance(A.SpatialLocation.ToString())/1000) <=700 THEN '4'
WHEN (@Location.STDistance(A.SpatialLocation.ToString())/1000) > 700 AND (@Location.STDistance(A.SpatialLocation.ToString())/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
)
GO
SELECT *
FROM DistanceSales()

当我运行这部分代码时，我应该得到一个表，其中包含我在函数中使用的所有信息。但是，由于我的声明变量，该函数将不会运行。我应该在函数中使用begin来包含declare吗？

Answer 1

这里我们只是在CROSS JOIN中抛出Location表达式

CREATE FUNCTION dbo.DistanceSales() RETURNS TABLE
AS
RETURN(
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((L.Location.STDistance(A.SpatialLocation.ToString())/1000),'N2') AS Distance,
CASE
WHEN (L.Location.STDistance(A.SpatialLocation.ToString())/1000)  <= 10 THEN '1'
WHEN (L.Location.STDistance(A.SpatialLocation.ToString())/1000) > 10 AND (L.Location.STDistance(A.SpatialLocation.ToString())/1000) <=50 THEN '2'
WHEN (L.Location.STDistance(A.SpatialLocation.ToString())/1000) > 50 AND (L.Location.STDistance(A.SpatialLocation.ToString())/1000) <=300 THEN '3'
WHEN (L.Location.STDistance(A.SpatialLocation.ToString())/1000) > 300 AND (L.Location.STDistance(A.SpatialLocation.ToString())/1000) <=700 THEN '4'
WHEN (L.Location.STDistance(A.SpatialLocation.ToString())/1000) > 700 AND (L.Location.STDistance(A.SpatialLocation.ToString())/1000) <=1000 THEN '5'
ELSE 'MAX'
END AS GroupDistance
FROM Sales S
CROSS JOIN ( SELECT Location = GEOGRAPHY::Point(87.63945,-187.12826,4326) ) L
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
)
GO