Laboratorium3

USE TSQL2012
GO

DROP PROCEDURE dbo.CustomerOrdersReport
GO

CREATE PROCEDURE CustomerOrdersReport
    @customer VARCHAR(128),
    @datefrom DATE,
    @dateto DATE

AS
    BEGIN
           SET @customer = (SELECT sc.companyname FROM Sales.Customers sc WHERE sc.companyname = @customer)
           SET @datefrom = (SELECT * FROM Sales.Orders so WHERE so.orderdate = @datefrom)
           SET @dateto = (SELECT * FROM Sales.Orders so WHERE so.orderdate = @dateto)
    END

DROP PROCEDURE  dbo.deleteOrder
GO

CREATE PROCEDURE deleteOrder
    @orderid INT = NOT NULL,
    @poscount INT,
    @posvalue INT
AS
    BEGIN
       SET NOCOUNT ON;
       IF NOT EXISTS(SELECT orderid FROM SAles.OrderDetails WHERE orderid = @orderid)
           BEGIN
            RAISERROR('There is no order with this id or its equals 0', 16 , 1)
           END;
       ELSE IF((SELECT count(orderid) FROM Sales.OrderDetails WHERE orderid = @orderid) > @poscount)
            BEGIN
                RAISERROR('Number of these orders are greater than provided number', 16, 1)
            END;
       ELSE IF((SELECT SUM(qty * unitprice) FROM Sales.OrderDetails WHERE orderid = @orderid) > @posvalue)
            BEGIN
               RAISERROR('The sum of orders is greater than provided', 16, 1)
            END;
       ELSE
            BEGIN
                DELETE FROM Sales.OrderDetails WHERE orderid = @orderid
                PRINT 'Successfully Deleted'
            END;
    END

DROP PROCEDURE NewProduct
GO

CREATE PROCEDURE NewProduct
    @productname VARCHAR(28),
    @categoryname VARCHAR(28),
    @unitprice INT,
    @suppliername VARCHAR(28)
AS
    BEGIN
        SET NOCOUNT ON;
        IF NOT EXISTS(SELECT * FROM Production.Products WHERE productname = @productname)
            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
        ELSE
            BEGIN
                RAISERROR('This product is already exists', 1, 16)
            END
    END