Re: New to DB2/400 Procedures

OK, your example worked...but my slight variation does not:

Create procedure PRODDTA.spGetContactTest2(in @DOCOID int,  in @ConID
smallint )
 Language        SQL
 modifies        SQL DATA
 result sets     1

 SET OPTION  COMMIT = *NONE

 BEGIN
IF EXISTS(
SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE WHERE NDOCO=@DOCOID AND
NHIWCONTACTTYPEID=@CONID
)
THEN
 declare c1 cursor with return for
SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
INNER JOIN RODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
t3.nhiwcontacttypeid
WHERE NDOCO = @DOCOID AND t1.NHIWCONTACTTYPEID = @CONID
 for read only ;

 open c1 ;

ELSE

 declare         c1 cursor with return for
 SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
 INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
 INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid
=t3.nhiwcontacttypeid
 WHERE NDOCO = @DOCOID AND t1.NHIWCONTACTTYPEID = 7
 for read only ;

 open c1 ;

END IF;
end

I'm getting the error:

[SQL0104] Token C1 was not valid. Valid tokens: GLOBAL. Cause . . . . . :
A syntax error was detected at token C1.  Token C1 is not a valid token.  A
partial list of valid tokens is GLOBAL.  This list assumes that the
statement is correct up to the token.  The error may be earlier in the
statement, but the syntax of the statement appears to be valid up to this
point. Recovery  . . . :   Do one or more of the following and try the
request again: -- Verify the SQL statement in the area of the token C1.
Correct the statement.  The error could be a missing comma or quotation
mark, it could be a misspelled word, or it could be related to the order of
clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL
statement because it does not end with a valid clause.
"Richter,Steve" <Steve.Richter@xxxxxxxxxxxxxxx>
wrote in message
news:7995C7E1B1CC454AA7006B86973DD44703561B67@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> try something like this Ryan:
>
>  Create procedure testproc1(
>  in @DOCOID int,
>   in @ConID smallint )
>  Language        SQL
>  modifies        SQL DATA
>  result sets     1
>
>  SET OPTION  COMMIT = *NONE
>  BEGIN
>
>  declare         c1 cursor with return for
>
>  SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
>  INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
>  INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
> t3.nhiwcontacttypeid
>  WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = 7
>
>  for read only ;
>
>  open            c1 ;
>  end
>
> -----Original Message-----
> From: Ryan Hunt [mailto:ryan.hunt@xxxxxxxxxxxxx]
> Sent: Wednesday, May 03, 2006 11:45 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: New to DB2/400 Procedures
>
>
> OK, I have lots of reading to do...I know that.  I'm completely unfamilar
> with the way DB2 result sets and flow-control language are used in SP's
(as
> I understand it, DB2 requires the use of cursors - probably based on the
> ANSI standard).
>
> Can someone help me get jump started here...  Below is a Proc using MS
SQL's
> T-SQL (which is what I'm used to.)  Can someone do a quick translation?
>
>
> CREATE PROCEDURE spGetContact (@DOCOID int, @ConID smallint)
> AS
>
> DECLARE @NDOCOID Integer
> DECLARE @CONTACTID SmallInt
>
> SET @NDOCOID=@DOCOID
> SET @ContactID=@ConID
>
> IF EXISTS(
> SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE WHERE NDOCO=@NDOCOID AND
> NHIWCONTACTTYPEID=@CONTACTID
> )
> BEGIN
>
>  SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
>  INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
>  INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
> t3.nhiwcontacttypeid
>  WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = @CONTACTID
> END
>
> ELSE
> BEGIN
>  SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
>  INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
>  INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
> t3.nhiwcontacttypeid
>  WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = 7
>
> END
>
> GO
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>