How to create a DB2 stored procedure in SQL with following conditions?

Question

I am new to this. Please do not downvote question.

I have created a table named 'FormData' which contains following fields Reviewer, AgencyName, Name, Email, Phone and all of type varchar. I have to retrieve all rows from FORMDATA where Reviewer is equal to the value of Reviewer given by the user.

I have written following code but I am not getting what is the problem in this.

CREATE PROCEDURE GetFormData( INOUT Reviewer varchar,
                              INOUT AgencyName varchar,
                              INOUT Name varchar,
                              INOUT Email varchar,
                              INOUT Phone varchar)
LANGUAGE SQL
P1:BEGIN
DECLARE v_Reviewer varchar;
SET v_Reviewer=Reviewer;
SELECT * FROM TRAININGDB.FormData
WHERE Reviewer=v_Reviewer;
END P1

The error I received after this is:

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 "Reviewer varchar"

We are using DB2 in WebSphere server and the remaining details are. Product Identifier: SQL09075 DB2 v9.7.500.702 JDK 1.6 OS: Windows 7

See if this helps: stackoverflow.com/questions/24758349/db2-function-error/… — mustaccio
– mustaccio, Commented Aug 9, 2017 at 14:21

mao · Accepted Answer · 2017-08-09 09:50:43Z

3

There are several problems with your code, and also you omit key details from your question ( the tool(s) you are using/versions/operating-system details).

SQL0104N happens because you did not configure the tool you used to submit the statement to specify an alternate statement delimiter. Inside the procedure the intra-statement delimiter is semicolon, but you need an additional delimiter to mark the end of the procedure (and configure the tool that submits the statement to specify that additional delimiter).

As you are learning, the best advice is to study the IBM example SQL PL stored procedures , and make them work on your environment. These are visible both in the online Knowledge Center for your version of DB2 and they are also installed on any DB2 Linux/Windows/Unix server.

Below is an example of your procedure if you submit it from the shell command line on Unix or Windows (db2cmd.exe or the bash shell). Lookup the syntax for all that is different from your versions and adjust it as needed.

--#SET TERMINATOR @
CREATE or replace PROCEDURE GetFormData( IN p_Reviewer varchar(20))
result sets 1
specific GetFormData
LANGUAGE SQL
P1:
BEGIN
    declare c1 cursor for SELECT * FROM TRAININGDB.FormData WHERE Reviewer=p_Reviewer;
    open c1;
END P1
@

answered Aug 9, 2017 at 9:50

mao

12.4k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jay Over a year ago

I have edited the details at the end of my question but this code provided by you is working just fine. Thanks for the help. And please don't downvote the question.

Neil Gaetano Lindberg Over a year ago

I found this a superior kick-off point to my own mission. Superior to IBM Support even! Just wanted to note that you can start messing with Stored Procedures on an IBM Warehouse Cloud DB2 via the SQL WYSIWYG editor. Once on the editor page you can click (as of today) a little circle plus button next to the tab. There, alas, there is an optional, "Template - SQL Stored Procedure". Coming from a PostgreSQL background I needed this as a functional example to start off, so sharing here for others.

Collectives™ on Stack Overflow

How to create a DB2 stored procedure in SQL with following conditions?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related