CREATE PROCEDURE create_order(IN inCustomerId INT, IN inProductId INT)
BEGIN
  DECLARE orderId INT;
    
  DECLARE exit handler for sqlwarning rollback;
  DECLARE exit handler for sqlexception rollback;

  START TRANSACTION;

  -- Insert a new record into orders and obtain the new order ID
  INSERT INTO tbl_orders (order_date, customer_id) 
       VALUES (NOW(), inCustomerId);
  -- Obtain the new Order ID
  SELECT LAST_INSERT_ID() INTO orderId;
  
  -- Insert order items in order_items table
  INSERT INTO tbl_order_items (order_id, pd_id, pd_name, qty, pd_price)
  SELECT orderId
       , pd.pd_id
       , pd.pd_name
       , pd.pd_qty
       , pd.pd_price
   FROM tbl_products pd
  WHERE pd.pd_id = inProductId;
  
  -- Insert credits in to credits acct only if pd type is 1, 2 or 3
  INSERT INTO tbl_customer_credit_acct (customer_id, pd_id, qty)
       SELECT inCustomerId, pd.pd_id, pd.qty
  		   FROM tbl_products pd
        WHERE pd.pd_id = inProductId AND (pd.pd_type = 1 OR pd.pd_type = 2 OR pd.pd_type = 3);
 
   
   -- Return the Order ID
  SELECT orderId; 
  
  COMMIT;

DECLARE continue handler for not found;

DECLARE exit handler for SQLSTATE '01000' set @_err = '01000';

Query OK, 0 rows affected, 1 warning (0.08 sec)

Warning (Code 1329): No data - zero rows fetched, selected, or processed

  DECLARE exit handler for not found rollback;
  DECLARE exit handler for sqlwarning rollback;
  DECLARE exit handler for sqlexception rollback;

-- check if value exists
Select value from tbl_test where value = 1 into check;

IF check = 1 THEN
  -- execute next statement
ELSE
  -- execute next statement
END IF;