帶有宣告變數的SQL函式

我必須在 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 來包含宣告嗎？

uj5u.com熱心網友回復：

這里我們只是把Location運算式折騰到一個CROSS JOIN

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

標籤：sqlsql服务器功能宣布

上一篇：除錯應將摩爾斯電碼翻譯成英語的函式時遇到問題

下一篇：JS：一個函式和兩個Document.getElementById()結果相同在“未定義”