Writing a stored procedure in command editor

db2learner

Hi,
I am new to DB2 and i just started worked on it a couple of days back. I have created basic EMPLOYEE table from control centre which has 2 fields: EmpNo, EmpName.

I am trying to write a simple stored proc in db2 command editor to insert values into these fields.

CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@

And when i execute this i get this error:

------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@;
------------------------------------------------------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ')
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 "END-OF-STATEMENT" was found following "E
VALUES(001, 'XYZ')". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601

END @
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 "END-OF-STATEMENT" was found following "END @".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

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

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". 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

Can someone help me in solving this problem.

Thanks.

rinkum2007db2

Hi,

I think the problem is coming because of the delimeter.
There should be a ; after End if you are not changing the delimeter.

Try this code
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END;
@

Regards,
rinku

Quote:

Originally Posted by db2learner

Hi,
I am new to DB2 and i just started worked on it a couple of days back. I have created basic EMPLOYEE table from control centre which has 2 fields: EmpNo, EmpName.

I am trying to write a simple stored proc in db2 command editor to insert values into these fields.

CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@

And when i execute this i get this error:

------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@;
------------------------------------------------------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ')
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 "END-OF-STATEMENT" was found following "E
VALUES(001, 'XYZ')". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601

END @
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 "END-OF-STATEMENT" was found following "END @".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

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

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". 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

Can someone help me in solving this problem.

Thanks.

Remi

Hi :

Send you a Proc Example
---------------------------------------

CREATE PROCEDURE RACE.SP_LISTARPAISES6(
IN OPCION VARCHAR(2) ,
IN CODPAIS VARCHAR(3) ,
IN DENPAIS VARCHAR(30),
IN TIP VARCHAR(1)
)
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE SQLSELE1 CHAR(512);
DECLARE C1 CURSOR WITH RETURN FOR S1;
-- DEBERAN PASAR EL VALOR DE CODPAIS INCLUYENDO EL PORCENTUAL '02%'--
IF TIP = '1' THEN
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ? FETCH FIRST 5000 ROWS ONLY ';
ELSE
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ?
FETCH FIRST 5000 ROWS ONLY';
END IF;
PREPARE S1 FROM SQLSELE1;
OPEN C1 USING CODPAIS;
END;

Writing a stored procedure in command editor

re: Writing a stored procedure in command editor

re: Writing a stored procedure in command editor

/bytes/development

/bytes/it

/bytes/about