Solved

Multiple insert statements in the same stored procedure

Posted on 2006-11-09
2
499 Views
Last Modified: 2008-02-01
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
Comment
Question by:ChadMarsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 17909475
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
 
LVL 2

Author Comment

by:ChadMarsh
ID: 17913829
Thanks a lot. That worked perfectly.
Chad
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question