Procedure creation problems

[blakmk]

Im not sure whats going on but when i create the following procedure using the development center it compiles ok.


CREATE PROCEDURE GIRS.PR_GetNextSeqNumber ( IN i_SequenceName VARCHAR(50),
OUT o_SequenceNumber INTEGER )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- SequenceName
-- SequenceNumber Next sequence number
------------------------------------------------------------------------
P1: BEGIN
--
UPDATE SystemSequences
SET CurrentSequenceNo = CurrentSequenceNo + 1
WHERE SequenceName = i_sequencename;
--
SELECT CurrentSequenceNo INTO o_SequenceNumber
FROM SystemSequences
WHERE SequenceName = i_SequenceName;
--
END P1


However when I try to create the same procedure using the command center or the db2 command line (using db2 -tvf scriptname.sql) I get the following error:


SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "END P1 ". Expected tokens may include: "JOIN
<joined_table> &quot;.

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text &quot;<text>&quot;. The &quot;<text>&quot; field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as &quot;<token-list>&quot;.
This list assumes the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601



Im running db2 8.1 personal edition on windows 2000

 

[jnelms]

The difference is in the statement terminator. By default you are using ';' at the command line.

Note that the SQL procedure languages uses ';' to terminate individual SQL statements. From a Create Procedure perspective the entire body (all SQL statements that make up the procedure) is one SQL statement.

To execute the Create Procedure from a command line set the statement terminator to be something like '%' and modify the input file to have the terminator character at the end. Then run DB2 -td% -f inputfile.sql. Note keep the ';' in the procedure body because that is what the interpreter expects to terminate SQL statements.