jeudi 26 octobre 2017

In CREATE FUNCTION, validate if parameters value exist in SQL then applied condition if not exist show all values

I don't know why my SQL code doesn't work, how can I correct it. I'm looking a way to applied condition on the year asked but if it doesn't exist, it should show all year. How ever, the code it's not working

CREATE FUNCTION SALES_YEAR(@year int=0) RETURNS TABLE
AS 
RETURN
IF EXISTS(SELECT 1 FROM AdventureWorks2014.Sales.SalesOrderHeader where 
Year(OrderDate)=@year)
SELECT Year(OrderDate) as 
[YEAR],SOH.TerritoryId,convert(varchar,SUM(SOH.SubTotal),1)as VenteTotal from AdventureWorks2014.Sales.SalesOrderHeader SOH
    GROUP BY Year(OrderDate),SOH.TerritoryID;

ELSE
    SELECT Year(OrderDate) as [year],SOH.TerritoryId,convert(varchar,SUM(SOH.SubTotal),1)+'$'as VenteTotal from AdventureWorks2014.Sales.SalesOrderHeader SOH
    Where Year(OrderDate)=@year or @year=0
    GROUP BY Year(OrderDate),SOH.TerritoryID

END)

Aucun commentaire:

Enregistrer un commentaire