lab3.20316

--4 zad--

IF EXISTS (SELECT * FROM sys.procedures WHERE name= 'ProductAdd' AND type='P')
    DROP PROCEDURE [dbo].[ProductAdd]
GO

Create procedure dbo.ProductAdd
    @productname nvarchar(20) = NULL,
    @categoryname nvarchar(20) = NULL,
    @unitprice money = NULL,
    @suppliername nvarchar(20) = NULL
AS
set nocount on;
IF EXISTS (SELECT productname FROM Production.Products WHERE @productname = productname)
    PRINT 'Już istnieje produkt o takiej nazwie'
ELSE
    BEGIN
        INSERT INTO Production.Products (productname,supplierid,categoryid, unitprice)
        VALUES (@productname,
                (SELECT supplierid FROM Production.Suppliers WHERE companyname = @suppliername ),
                (SELECT categoryid FROM Production.Categories WHERE categoryname = @categoryname),
                @unitprice)
    END;

EXEC dbo.ProductAdd @productname = 'Product XXXCC', @categoryname = 'Seafood', @unitprice = 123, @suppliername = 'Supplier OGLRK'

--3 zad: discount nie brałem pod uwagę--

IF EXISTS (SELECT * FROM sys.procedures WHERE name= 'deleteOrder' AND type='P')
    DROP PROCEDURE [dbo].[deleteOrder]
GO

CREATE PROCEDURE dbo.deleteOrder
    @orderid int,
    @poscount int = NULL,
    @posvalue money = NULL
AS
set nocount on;
IF NOT EXISTS (SELECT orderid FROM Sales.OrderDetails WHERE orderid = @orderid)
    PRINT 'Nie istnieje zamówienia o takim id lub podany id jest równy zero'
ELSE IF ((SELECT count(orderid) FROM Sales.OrderDetails WHERE orderid = @orderid) > @poscount)
    PRINT 'Liczba takich zamówień jest większa od podanej liczby'
ELSE IF ((SELECT SUM(qty*unitprice) FROM Sales.OrderDetails WHERE orderid = @orderid ) > @posvalue)
    PRINT 'Suma zamówienia jest większa od podanej'
ELSE
    BEGIN
    DELETE FROM Sales.OrderDetails WHERE orderid = @orderid;
    PRINT 'Deleted successfully'
    END

EXEC dbo.deleteOrder @orderid = 10324, @poscount = null, @posvalue = 6200