CODEWITHSUNDEEP
sqlstored-proceduresdb2squirrel-sql
developer234
developer234

Reputation: 79

db2 stored procedure format

I'm using db2 and SQuirreL SQL

I'm trying to create a stored procedure with a simple select statement inside of it. When I omit the select statement in the following below and run the code, the procedure is created. Also this procedure can be dropped and called.

    CREATE PROCEDURE test_procedure
    LANGUAGE SQL
    BEGIN

    END

When I add in the select statement, I get Error: DB2 SQL Error: SQLCODE=-102, SQLSTATE=42601,... 

    CREATE PROCEDURE test_procedure
    LANGUAGE SQL
    BEGIN
        SELECT column_name FROM table_name
    END

If you go to IBM iseries information center is says:

SQL0104 SQLCODE -104 SQLSTATE 42601

Explanation: Token &1 was not valid. Valid tokens: &2.

Upvotes: 1

Views: 2402

Answers (2)

developer234
developer234

Reputation: 79

It appears that I wasn't given the right permissions to execute the stored procedure. SQL0551N This link explains more about the issue.

Upvotes: 1

AngocA
AngocA

Reputation: 7693

The statement terminator in SQuirreL is called "Statement separator" and it can be defined in:

Menu Session > Session Properties... > tab SQL > at the end of the SQL square, the option Statement Separator.

enter image description here

This is valid in version 3.5.3

Upvotes: 0

Related Questions