Calculates the sum to pay of the specific order
-------------------------------------------------------------------------------
CREATE PROCEDURE calculatingSum @orderID INT, @price INT OUTPUT
AS
DECLARE @orderQuantity INT, @orderProdPrice INT, @currProductId INT, @orderSum INT = 0, @orderCount INT
DECLARE currentOrder CURSOR
FOR
SELECT Order_items_product, Order_items_quantity FROM Order_items WHERE Order_id = @orderID;
SELECT @orderCount = COUNT(Order_id) FROM Order_items WHERE Order_id = @orderID;
IF @orderCount != 0
BEGIN
OPEN currentOrder
FETCH NEXT FROM currentOrder INTO @currProductID, @orderQuantity
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @orderProdPrice = Product_price FROM Products WHERE Product_id = @currProductId
SET @orderSum = @orderSum + ( @orderQuantity * @orderProdPrice )
FETCH NEXT FROM currentOrder INTO @currProductID, @orderQuantity
END
END
CLOSE currentOrder
DEALLOCATE currentOrder
SET @price = @orderSum
IF @orderCount = 0
BEGIN
SET @price = 0
PRINT 'There is no such order'
END

------------------------------------------------------------------------------'

Calculates money that customer has spent for all orders
-----------------------------------------------------------------
CREATE PROCEDURE customerMoneySpent @customer INT
AS
DECLARE @totalAmount INT = 0, @customerCount INT, @orderSum INT, @orderId INT
DECLARE orders CURSOR
FOR
SELECT Order_id FROM Orders WHERE Order_customer = @customer
SELECT @customerCount = COUNT(Order_id) FROM Orders WHERE Order_customer = @customer
IF @customerCount != 0
BEGIN
OPEN orders
FETCH NEXT FROM orders INTO @orderId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC calculatingSum @orderId, @price = @orderSum OUTPUT
SET @totalAmount = @totalAmount + @orderSum
FETCH NEXT FROM orders INTO @orderId
END
CLOSE orders
DEALLOCATE orders
PRINT 'Customer: ' + cast(@customer as VARCHAR) + ' has spent: ' + cast(@totalAmount as VARCHAR)
END
IF @customerCount = 0
BEGIN
PRINT 'There is no such customer'
END
------------------------------------------------------------------------------------

Finding customers that have spent more money than @x  (RESULT SET)

----------------------------------------------------------------------------

CREATE PROCEDURE customerSpentMoreThan @x INT
AS
DECLARE @custID INT, @moneySpent INT
DECLARE currentCustomer CURSOR
FOR
SELECT DISTINCT Order_customer FROM Orders;
OPEN currentCustomer
FETCH NEXT FROM currentCustomer INTO @custID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC customerMoneySpentOutput @custID, @totalOut = @moneySpent OUTPUT
INSERT INTO Customer_spent VALUES (@custID, @moneySpent)
FETCH NEXT FROM currentCustomer INTO @custID
END
CLOSE currentCustomer
DEALLOCATE currentCustomer
IF (SELECT count(Customer_spent_id) FROM Customer_spent WHERE Customer_spent_money > @x) > 0
BEGIN
SELECT * FROM Customer_spent WHERE Customer_spent_money > @x
DELETE FROM Customer_spent
END
ELSE
PRINT 'There is no one who earns more than: '  + cast(@x as VARCHAR)
^
|
|
|
CREATE PROCEDURE customerMoneySpentOutput @customer INT, @totalOut INT OUTPUT
AS
DECLARE @totalAmount INT = 0, @orderSum INT, @orderId INT
DECLARE currOrder CURSOR
FOR
SELECT Order_id FROM Orders WHERE Order_customer = @customer
OPEN currOrder
FETCH NEXT FROM currOrder INTO @orderId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC calculatingSumOut @orderId, @price = @orderSum OUTPUT
SET @totalAmount = @totalAmount + @orderSum
FETCH NEXT FROM currOrder INTO @orderId
END
CLOSE currOrder
DEALLOCATE currOrder
SET @totalOut = @totalAmount
^
|
|
|
CREATE PROCEDURE calculatingSumOut @orderID INT, @price INT OUTPUT
AS
DECLARE @orderQuantity INT, @orderProdPrice INT, @currProductId INT, @orderSum INT = 0
DECLARE currentOrder CURSOR
FOR
SELECT Order_items_product, Order_items_quantity FROM Order_items WHERE Order_id = @orderID;
OPEN currentOrder
FETCH NEXT FROM currentOrder INTO @currProductID, @orderQuantity
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @orderProdPrice = Product_price FROM Products WHERE Product_id = @currProductId
SET @orderSum = @orderSum + ( @orderQuantity * @orderProdPrice )
FETCH NEXT FROM currentOrder INTO @currProductID, @orderQuantity
END
CLOSE currentOrder
DEALLOCATE currentOrder
SET @price = @orderSum
-----------------------------------------------------------------

finding customer with max spent money (RETURN TYPE)
---------------------------------------------------------
CREATE PROCEDURE findMaxSpent
AS
DECLARE currentCustomer CURSOR
FOR
SELECT DISTINCT Order_customer FROM Orders
DECLARE @maxID INT, @custID INT, @moneySpent INT
OPEN currentCustomer
FETCH NEXT FROM currentCustomer INTO @custID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC customerMoneySpentOutput @custID, @totalOut = @moneySpent OUTPUT
INSERT INTO Customer_spent VALUES (@custID, @moneySpent)
FETCH NEXT FROM currentCustomer INTO @custID
END
CLOSE currentCustomer
DEALLOCATE currentCustomer
IF (SELECT count(Customer_spent_id) FROM Customer_spent) > 0
BEGIN
SELECT @maxID = Customer_spent_id FROM Customer_spent WHERE Customer_spent_money =
(SELECT max(Customer_spent_money) FROM Customer_spent)
DELETE FROM Customer_spent
RETURN @maxID
END
---------------------------------------------------------