BD3_K1_Z2_g1

--2

create or alter procedure UpdateBestsellers
    @kategoria varchar(10) = null,
    @year int
as
begin

    if  not exists (select * from Production.Categories where categoryname = @kategoria)
        insert into Production.Categories (categoryname, description)
        values (@kategoria, '-----')

    declare @newCatId int
    set @newCatId = (select top 1 categoryid from Production.Categories where categoryname = @kategoria)

    update Production.Products set categoryid = @newCatId where productid in (
        select top 3 sod.productid from Sales.OrderDetails sod
        inner join Sales.Orders so on sod.orderid = so.orderid
        where year(so.orderdate) = @year
        group by productid order by sum(qty) desc )
end
go

exec UpdateBestsellers @kategoria = 'Top2006', @year = 2006

select * from Production.Categories
select * from Production.Products where categoryid = 13 -- tutaj zamiast 13 nowe id z Production.Categories