Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Posts
    34

    Unanswered: stored procedure - help

    david2002, posted a question but I was unable to reply to that thread.

    Here are 2 examples stored procedures in sql. There is very little documentation out there on stored procedures. Hope this helps.

    These stored procedure reads from 1 table has some minimal business logic, and then inserts into a target table or an error handling table.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2002
    Posts
    34
    the attached stored proc deletes records and commits every n records.

    NOTE: you should always commit every n records in stored procedures, and in doing you need to declare the cursor "DECLARE <cursor_name> CURSOR WITH HOLD FOR" and then close the cursor with "CLOSE <cursor_name> WITH RELEASE"
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2002
    Posts
    86
    Thank you Brian for your response,
    I run the second script in AIX DB2v7.2 and I got these kind of error:
    CREATE PROCEDURE SIEBEL.DELETE_EIM_PROD_SHIP(IN BATCH INT)
    LANGUAGE SQL

    BEGIN
    DECLARE v_rowId VARCHAR(50)
    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 "v_rowId
    VARCHAR(50)". Expected tokens may include: "<psm_semicolon>". LINE
    NUMBER=5. SQLSTATE=42601

    DECLARE v_rowCount INT DEFAULT 1
    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 "v_rowCount" was found following "DECLARE ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    DECLARE at_end INT DEFAULT 0
    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 "at_end" was found following "DECLARE ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    DECLARE not_found CONDITION FOR SQLSTATE '02000'
    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 "not_found" was found following "DECLARE ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
    Please Advise.
    Thanks

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Terminate the CREATE PROCEDURE Statement with a @ (ie END @ ) and then

    db2 -td@ -f <filename>

    HTH

    Cheers

    Sathyaram

  5. #5
    Join Date
    Jul 2002
    Posts
    86
    Thank you Sathyaram_s for you help,
    I did as you said I got error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL10013N The specified library "db2udp" could not be loaded. SQLSTATE=42724

    Please Advise.
    Thanks

  6. #6
    Join Date
    Aug 2002
    Posts
    1

    Angry not allowed to change the terminating character - what now?

    I'm not allowed to change the terminating character (stupid business practice). How can I create a stored procedure through SQL without changing it? ANY help would be greatly appreciated. I've been wracking my brain for far too long now.

    Thanx,
    Rizzi

  7. #7
    Join Date
    Mar 2002
    Posts
    34
    david,

    sorry I should of clarified, I run this procedure in the "stored procedure builder" not the command line. I am assuming this is the reason for the error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •