CREATE PROCEDURE DBA.ProductType( @product_ID INTEGER,@TYPE CHAR(10) OUTPUT ) AS
BEGIN
  DECLARE @prod_name CHAR(20)
  SELECT Name INTO @prod_name FROM Products
    WHERE ID = @product_ID
  IF @prod_name
   = 'Tee Shirt'
    SET @TYPE = 'Shirt'
  ELSE IF @prod_name
   = 'Sweatshirt'
    SET @TYPE = 'Shirt'
  ELSE IF @prod_name
   = 'Baseball Cap'
    SET @TYPE = 'Hat'
  ELSE IF @prod_name
   = 'Visor'
    SET @TYPE = 'Hat'
  ELSE IF @prod_name
   = 'Shorts'
    SET @TYPE = 'Shorts'
  ELSE
    SET @TYPE = 'UNKNOWN'
END;

CREATE PROCEDURE DBA.StockLevel( @product_ID INTEGER ) AS
BEGIN
  DECLARe @qty INTEGER
  SELECT Quantity INTO @qty FROM Products
    WHERE ID = @product_ID
  IF @qty < 30
    MESSAGE 'Order Stock' TO CLIENT
  ELSE IF @qty > 100
    MESSAGE 'Overstocked' TO CLIENT
  ELSE
    MESSAGE 'Sufficient stock on hand' TO CLIENT
END;