Nisar Nisar - 1 year ago 45
MySQL Question

My sql error in creating temporaty table stored procedure

I have a stored procedure query and inserting value in temp table.. but shows error

delimiter //
create procedure AccountLedgerViewUnderSundryDebtorCreditorCash()
create temporary table temp_kk(accountGroupID varchar(50),HierarchyLevel varchar(50));

insert into temp_kk(accountGroupID,HierarchyLevel)
select accountGroupId, 1 as HierarchyLevel from tbl_AccountGroup
where accountGroupId='27'or accountGroupId = '28'or
accountGroupId = '11';

create temporary table temp_kk2(accountGroupID varchar(50),HierarchyLevel varchar(50));

insert into temp_kk2(accountGroupID,HierarchyLevel)
select e.accountGroupId, G.HierarchyLevel + 1 AS HierarchyLevel
from tbl_AccountGroup as e, temp_kk G
where e.groupUnder=G.accountGroupId ;

create temporary table temp_kk3(accountGroupID varchar(50),HierarchyLevel varchar(50));

insert into temp_kk3(accountGroupID,HierarchyLevel)
select * from temp_kk union all temp_kk2 ;

ledgerId AS 'Account Ledger Id',
accountLedgerName AS 'Account Ledger Name',
accountGroupId AS 'Account Group Id',
openingBalance AS 'Opening Balance',
debitOrCredit AS 'Credit Or Debit',
defaultOrNot AS 'Editable Or Not',
description AS 'Description'

FROM tbl_AccountLedger
where accountGroupId IN (select accountGroupId from temp_kk3);

end //
delimiter ;

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'temp_kk2 ; SELECT ledgerId AS [Account Ledger Id], acccountLedgerName' at line 20

Answer Source

This line:

    select * from temp_kk union all temp_kk2 ;

Needs to be:

    select * from temp_kk union all select * from temp_kk2 ;

At least, that what sticks out most obviously.

Though, I tend to avoid using select *. If you change the temp table, it will break. It also ends up making the code harder to read if you don't know for sure what all columns are in the table you're selecting from. Yes, it saves you some typing right now, but you'll lose that time-savings later on when you have to keep looking back at the table definition to figure out what exactly you're selecting.

Though, with these simple temp tables, it isn't as big of a deal.