RE: Stored Procedure on iSeries.

Is the semi-colon required at the end of the IF EXISTS (SELECT * FROM
MYDB.EMAIL WHERE EMAIL_ID = em;) statement?


Jeff Stevens
Mize, Houser & Co. P.A.
913 451 1882
JStevens@xxxxxxxxxxxxxx





"Phil Kestenbaum" <pkestenbaum@xxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
04/30/2007 02:34 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Stored Procedure on iSeries.






Yes my mistake I wonder if it needs parens?

-----Original Message-----
From: rpg400-l-bounces+pkestenbaum=sditech.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+pkestenbaum=sditech.com@xxxxxxxxxxxx] On Behalf
Of Misty Thornton
Sent: Monday, April 30, 2007 3:34 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Stored Procedure on iSeries.

Thanks for the response. My update statement is already in place, right?

Kindest regards,
Misty.


On 4/30/07, Phil Kestenbaum <pkestenbaum@xxxxxxxxxxx> wrote:

I believe that you need to code an update statement as well.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Misty Thornton
Sent: Monday, April 30, 2007 3:20 PM
To: RPG programming on the AS400 / iSeries
Subject: Stored Procedure on iSeries.

Hey All;

I am trying to create a stored procedure which will insert a record

into

a
file if the record does not exist and updates the record if the record
already exists. I am only passing one parameter to the stored

procedure

VARCHAR(10). My EMAILS file has only two fields, EMAIL_ID varchar(10)
and
EMAIL_DESC varchar(25). This is how I am trying to code but I keep
getting
the following error.

***********

DB21034E The command was processed as an SQL statement because it was
not a

valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token ";" was found following "E WHERE

EMAIL_ID

=
em".
Expected tokens may include: "<space>". LINE NUMBER=7.

SQLSTATE=42601

***********



CREATE PROCEDURE MYDB.MANAGE_EMAILS

( IN em VARCHAR(10))
SPECIFIC MYDB.MANAGE_EMAILS

DYNAMIC RESULT SETS 1

P1: BEGIN
IF EXISTS (SELECT * FROM MYDB.EMAIL WHERE EMAIL_ID = em;)
THEN
UPDATE MYDB.EMAIL
SET EMAIL_DESC= 'YYY'
WHERE EMAIL_ID = em;
ELSE
INSERT INTO MYDB.EMAIL(EMAIL_ID, EMAIL_DESC)
VALUES (em, 'YYY')
END IF;
ELSE
UPDATE MYDB.EMAIL

SET EMAIL_DESC = 'YYY'
WHERE EMAIL_ID = em;
END IF
END P1



It almost seems like I am missing something really small here. Any

help

in
the right direction is much appreciated. TIA.

Kindest regards,

-Misty.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


This message contains information proprietary to our company. It is
intended to be read only by the individual or entity named above or

their

designee.
Any distribution of this message or the information contained herein
without written permission from our company is strictly prohibited. If

the

reader of this message is not the intended recipient or an agent

responsible

for delivering it to the intended recipient, you are hereby notified

that

you have received this document in error and that any review,

dissemination,

distribution, or copying of this message is strictly prohibited. If

you have

received this communication in error, please notify us immediately by
e-mail, and delete the original message.



--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.