CODEWITHSUNDEEP
sqlstored-proceduresdb2
Jay
Jay

Reputation: 63

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

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

Upvotes: 3

Views: 20063

Answers (1)

mao
mao

Reputation: 12287

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
@

Upvotes: 3

Related Questions