Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Multiple insert statements in the same stored procedure

Hello Experts,
I am trying to build a stored procedure in MySQL that runs 1 insert statement, get the identity, then runs another insert statement.
Here is my procedure:
CREATE PROCEDURE usp_sch_insert_subevent_attendees_dev(
      IN transaction_key int,
      IN attendee_key int,
      IN sub_event_key int,
      IN event_key int,
      IN subevent_comments varchar(500),
      IN fee int,
      IN comp int,
      IN comp_amt int,
      IN comp_code varchar(20),
      IN pmt_type char(10)
 )

BEGIN


INSERT INTO sch_invoice
(
transaction_key,
attendee_key,
sub_event_key,
fee,
comp,
comp_amt,
pmt_type
)
VALUES
(
transaction_key,
attendee_key,
sub_event_key,
fee,
comp,
comp_amt,
pmt_type
)


DECLARE i_key int
SET i_key = last_insert_id()



INSERT INTO sch_subevent_attendees
(
      attendee_key,
      sub_event_key,
      event_key,
      subevent_comments,
      fee,
      comp,
      comp_amt,
      comp_code,
      transaction_key,
      invoice_key,
      pmt_type
)
VALUES
(
      attendee_key,
      sub_event_key,
      event_key,
      subevent_comments,
      fee,
      comp,
      comp_amt,
      comp_code,
      transaction_key,
      i_key,
      pmt_type
)

END;

And here is the very vague error I'm getting:
Error Code : 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 'INSERT INTO sch_invoice
(
transaction_key,
attendee_key,
sub_event_key,
fee,
com' at line 14
(16 ms taken)

If I take one of the insert statements out, it runs OK. Is it not possible to do this in MySQL 5.0.22-standard?
Thanks
Chad
0
ChadMarsh
Asked:
ChadMarsh
1 Solution
 
snoyes_jwCommented:
You need ; after each insert and declaration.  You'll need to change the delimiter to something other than ; using the DELIMITER statement, so that MySQL will process the whole thing as one stored procedure.  See http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html for examples.
0
 
ChadMarshAuthor Commented:
Thanks a lot. That worked perfectly.
Chad
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now