DELIMITER //

CREATE PROCEDURE Nafnið_a_procedure()
BEGIN
  SELECT * FROM töfluna_sem_þu_vilt;
END //

DELIMITER ;


CALL Nafnið_a_procedure();


DELIMITER //

CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
  BEGIN
    SELECT * FROM offices WHERE country = countryName;
  END //
DELIMITER ;


CALL GetOfficeByCountry("uk");


DELIMITER //
CREATE PROCEDURE CountOrderByStatus(IN orderStatus VARCHAR(25),OUT total INT)
  BEGIN
    SELECT count(orderNumber) INTO total FROM orders WHERE status=orderStatus;
  END //
DELIMITER ;


CALL CountOrderByStatus("Shipped",@eiki);




DELIMITER $$

CREATE PROCEDURE get_order_by_cust(
  IN cust_no INT,
  OUT shipped INT,
  OUT canceled INT,
  OUT resolved INT,
  OUT disputed INT)
BEGIN

-- shipped
SELECT
  count(*) INTO shipped
FROM
  orders
WHERE customerNumber = cust_no AND status = 'Shipped';

-- canceled
SELECT
  count(*) INTO canceled
FROM
  orders
WHERE
  customerNumber = cust_no AND status = 'Canceled';

-- resolved

SELECT
  count(*) INTO resolved
FROM
  orders
WHERE customerNumber = cust_no AND status = 'Resolved';

SELECT
  count(*) INTO disputed
FROM
  orders
WHERE
  customerNumber = cust_no AND status = 'Disputed';

END $$

DELIMITER ;

CALL get_order_by_cust(141,@s,@c,@r,@d);


SELECT @s,@c,@r,@d;



DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN p_customerNumber int(11),
    OUT p_customerLevel varchar(10))
BEGIN
  DECLARE creditlim double;

  SELECT creditLimit INTO creditlim
  FROM customers
  WHERE customerNumber = p_customerNumber;

  IF creditlim > 50000 THEN
SET p_customerLevel = 'PLATINUM';
    ELSEIF(creditlim <= 50000 AND creditlim >= 10000) THEN
      SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
      SET p_customerLevel = 'SILVER';
  END IF;
END $$

DELIMITER ;




DELIMITER //

CREATE PROCEDURE Nafnið_a_procedure()
BEGIN
  SELECT * FROM töfluna_sem_þu_vilt;
END //

DELIMITER ;


CALL Nafnið_a_procedure();