T-SQL Programming Return table based on Variable

Question

I've been trying to make this work for a while now but I can't figure what's wrong.

The function needs to return a table based on the input date. If the input year is not available in the database, the function needs to return the data for all the years, otherwise, it needs to return the data only for the specified year.

Maybe someone here can help. I'm using the 2014 AdventureWorks Database.

Thanks

CREATE FUNCTION DBO.udf_fonction(@year INT)

RETURNS TABLE

AS

RETURN 

IF @year IN (SELECT DISTINCT(YEAR(OrderDate)) AS Years
         FROM Sales.SalesOrderHeader)

(
    SELECT YEAR(A.OrderDate) AS SalesYear,
    C.Name AS SalesTerritory, 
    D.Name AS SalesCountryName, 
    CASE(A.OnlineOrderFlag)
        WHEN 1 THEN 'Online'
        WHEN 0 THEN 'In-store'
    END AS SalesType,
    SUM(A.SubTotal) AS Montant

    FROM sales.SalesOrderHeader A
    INNER JOIN Sales.Customer B ON A.CustomerID = B.CustomerID
    INNER JOIN Sales.SalesTerritory C ON B.TerritoryID = C.TerritoryID
    INNER JOIN Person.CountryRegion D ON C.CountryRegionCode = D.CountryRegionCode
    WHERE YEAR(A.OrderDate) = @year
    GROUP BY YEAR(A.OrderDate), C.Name, D.Name, CASE(A.OnlineOrderFlag)
                                                    WHEN 1 THEN 'Online'
                                                    WHEN 0 THEN 'In-store'
                                                END
                                                )
ELSE (SELECT YEAR(A.OrderDate) AS SalesYear,
    C.Name AS SalesTerritory, 
    D.Name AS SalesCountryName, 
    CASE(A.OnlineOrderFlag)
        WHEN 1 THEN 'Online'
        WHEN 0 THEN 'In-store'
    END AS SalesType,
    SUM(A.SubTotal) AS Montant

    FROM sales.SalesOrderHeader A
    INNER JOIN Sales.Customer B ON A.CustomerID = B.CustomerID
    INNER JOIN Sales.SalesTerritory C ON B.TerritoryID = C.TerritoryID
    INNER JOIN Person.CountryRegion D ON C.CountryRegionCode = D.CountryRegionCode
    GROUP BY YEAR(A.OrderDate), C.Name, D.Name, CASE(A.OnlineOrderFlag)
                                                    WHEN 1 THEN 'Online'
                                                    WHEN 0 THEN 'In-store'
                                                END
                                                )
GO

Answer 1

WHERE YEAR(A.OrderDate) = @year 
   OR not exists (select 1 from sales.SalesOrderHeader where YEAR(OrderDate) = @year)

You are not returning a table anywhere