BD3_K1_Z3_g1

--3

create or alter function OrderedProducts(
    @categoryid INT = 0,
    @year INT = 2000
)
returns int
as
begin

    declare @result int = 0

    select @result = sum(sod.qty) from Sales.OrderDetails sod
        inner join Production.Products pp on sod.productid = pp.productid
        inner join Sales.Orders so on sod.orderid = so.orderid
        where year(so.orderdate) = @year and pp.categoryid = @categoryid

     return @result
end
go

select c.categoryname, dbo.OrderedProducts(c.categoryid, 2006) as 'Orders in 2006' from Production.Categories c