BPII - LAB 7

-- 1 INSERT PROCEDURA
CREATE PROCEDURE usp_Products_Insert
(
    @ProductName nvarchar (40),
    @SuplierID int=NULL,
    @CategoryID int=NULL,
    @QunatityPerUnit nvarchar (20) = NULL,
    @UnitPrice money = NULL,
    @UnitsInStock smallint = NULL,
    @UnitsOnOrder smallint=NULL,
    @RecorderLevel smallint=NULL,
    @Disontinues bit
)
AS
BEGIN
    INSERT INTO Products
    VALUES(@ProductName, @SuplierID, @CategoryID, @QunatityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @RecorderLevel, @Disontinues)
END

EXEC usp_Products_Insert @ProductName='Coca Cola' ,
                        @SuplierID=1 ,
                        @CategoryID=1,
                        @UnitPrice=5,
                        @UnitsInStock=50,
                        @UnitsOnOrder=0,
                        @Disontinues=1

--2 UPDATE PROCEDURA
CREATE PROCEDURE usp_Procedure_Update
(
    @ProductID int,
    @ProductName nvarchar (40),
    @SuplierID int=NULL,
    @CategoryID int=NULL,
    @QunatityPerUnit nvarchar (20) = NULL,
    @UnitPrice money = NULL,
    @UnitsInStock smallint = NULL,
    @UnitsOnOrder smallint=NULL,
    @RecorderLevel smallint=NULL,
    @Disontinues bit
)
AS
BEGIN
    UPDATE Products
    SET ProductName=@ProductName,
        SupplierID=@SuplierID,
        CategoryID= @CategoryID,
        QuantityPerUnit= @QunatityPerUnit,
        UnitPrice= @UnitPrice,
        UnitsInStock= @UnitsInStock,
        UnitsOnOrder= @UnitsOnOrder,
        ReorderLevel= @RecorderLevel,
        Discontinued= @Disontinues
    WHERE ProductID=@ProductID
END

EXEC usp_Procedure_Update  @ProductName='Coca Cola' ,
                        @SuplierID=1 ,
                        @CategoryID=1,
                        @UnitPrice=7,
                        @UnitsInStock=50,
                        @UnitsOnOrder=0,
                        @Disontinues=1,
                        @ProductID=81

SELECT * FROM Products WHERE ProductName LIKE 'Coca%'


-- 3 DELETE PROCEDURA
CREATE PROCEDURE usp_Procedure_Delete
(
    @ProductID int
)
AS
BEGIN
    DELETE FROM Products
    WHERE ProductID=@ProductID
END

EXEC usp_Procedure_Delete @ProductID=81
SELECT * FROM Products WHERE ProductName LIKE 'Coca%'


-- Kombinacija INSERT+UPDATE procedura
CREATE PROCEDURE usp_OrderDetails_Insert
(
    @OrderID int,
    @ProductID int,
    @UnitPrice money,
    @Quantity smallint,
    @Discount real
)
AS
BEGIN
    INSERT INTO [Order Details]
    VALUES(@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount)

    UPDATE Products
    SET UnitsInStock=UnitsInStock-@Quantity
    WHERE ProductID=@ProductID
END

EXEC usp_OrderDetails_Insert 10249, 1 ,2,5, 0.1

SELECT * FROM Products WHERE ProductID=1
SELECT * FROM Orders
SELECT * FROM [Order Details] WHERE OrderID=10249