SQL Function with Declare Variable-CodePudding

I have to create a function in SQL which doesn't take any parameter but which returns a table with multiple information. However, within my function I have to include a variable that I declared which contains coordinates. I thus have different information selected to include within my table but when I include the declare variable my function doesn't work.

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()

When I Run this portion of code, I should be getting a table with all the information I used in my function. However, the function won't run because of my declare variable. Should I be using a begin instead in my function to include the declare?

CodePudding user response：

Here we just toss Location expression in a 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