Main menu

Transaction Unit Balance Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:54

<?php
--
-- Transaction Unit Balance Stored Procedure
--

USE <

code>cics_app</code>;

DROP PROCEDURE IF EXISTS Transaction_UnitBalance;

DELIMITER |
CREATE PROCEDURE Transaction_UnitBalance (IN unit_number INT, IN unit_account_id INT)
BEGIN
   DECLARE unit_balance, unit_credit, unit_debit INT;

   

SELECT
        SUM(t.amount) INTO unit_credit
FROM
      transaction AS t
   WHERE
      t.unit_number = unit_number
      AND
      t.credit_account_id = unit_account_id;

   

SELECT
        SUM(t.amount) INTO unit_debit
  FROM
      transaction AS t
   WHERE
      t.unit_number = unit_number
      AND
      t.debit_account_id = unit_account_id;

SET unit_debit = IFNULL(unit_debit,0);
SET unit_credit = IFNULL(unit_credit,0);

 

SET unit_balance = unit_debit - unit_credit;

 

SELECT unit_balance;
END;
|
DELIMITER ;
?>
Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure