Error Code 1172. Result consisted of more than 1 row

[John Noble]

Hi folks,

I am having a problem with a sproc. I think I know what the problem is but am unsure on how to solve it.

I am trying to query a table, create a temp table from it whilst adding a running balance to it. I have marked where I think the problem is.


CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_BankTranShowTransactions`(
IN p_vesselID varchar(7),
IN p_account varchar(1))
BEGIN

declare v_credit, v_debit, v_balance decimal(13,2);
declare v_reference varchar(25);
declare v_vesselID varchar(7);
declare v_account varchar(1);
declare v_docDate date;
declare v_pKey int;


-- Get Data for processing.
select pKey, vesselID, account, docDate, reference, credit, debit, balance from bankTran WHERE vesselID = p_vesselID and account = p_account ORDER BY docDate into v_pKey, v_vesselID, v_account, v_docDate, v_reference, v_credit, v_debit, v_balance;
-------- Above is where the problem is. I think the "INTO" part of the statement is expecting a single row. But there will always be more than 1 row.


-- Create temp table (same struct as bankTRan)
DROP TEMPORARY TABLE IF EXISTS temp_BankTran;
CREATE TEMPORARY TABLE temp_BankTran (pKey int, vesselID varchar(7), account varchar(1), docDate date, reference varchar(25), credit decimal, debit decimal, balance decimal);

-- Set opening balance to 0
set v_balance = 0;

-- Loop through results and update balance amount and insert into temp table
bankTran_loop: loop

SET v_balance = v_balance + v_credit - v_debit;

insert into temp_BankTran values (v_pKey, v_vesselID, v_account, v_docDate, v_reference, v_credit, v_debit, v_balance);

end loop;

-- Select dataset for returning
select * from temp_BankTran;

-- Delete the temp table
DROP TEMPORARY TABLE IF EXISTS temp_BankTran;


END

Can anyone advise ??

Thanks,

John