Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Exclamation Unanswered: problem in syntax of stored procedure

    hi all,
    I'm creating a stored proc in ibm data studio ->database->application objects->stored procedure->new

    the code is as follows. it has syntax errors.I tried everything but it isn't improving -

    CREATE PROCEDURE AUTHENTICATEUSER (IN MEM_NAME VARCHAR(30), OUT LEAD_NAME VARCHAR(30))
    DYNAMIC RESULT SETS 1 LANGUAGE SQL
    BEGIN
    DECLARE p_mem, p_lead VARCHAR(30);
    set p_mem=mem_name;
    DECLARE rs CURSOR WITH RETURN FOR Select "ApplicantName","ProjectLead" FROM
    "dept" WHERE "ApplicantName" = p_mem;
    OPEN rs;
    FETCH FROM rs INTO p_mem, p_lead;
    END

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to post the exact text of the errors you are receiving.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2012
    Posts
    8

    errors

    Line 3 : "END" was expected to form a complete scope
    Line 4: "<psm_semicolon>" was expected to form a complete scope
    Line 8: "<join_type_without_spec>JOIN<join-operand>" was expected to form a complete scope
    Line 9: "JOIN" was expected instead of "rs"
    "<values>" was expected instead of "FETCH FROM rs INTO"
    "," was expected instead of ";"

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have not used Data Studio to create SP's, but in some tools (and in scripts) you must specify a different SQL seperator than the default semi-colon (since the create procedure command includes embeded semi-colons), and use that at the end of the SP. I normally use the @ symbol at the very end of the SP, and then tell my tool what the new SQL seperator symbol is.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Mar 2012
    Posts
    8

    Lightbulb rectify

    so rectify this query and plz tell me how'll u write this sp in ur format.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mudit.jha View Post
    I tried everything
    I suspect one thing you didn't try was reading the manual. All DECLARE statements must precede any other statement, as explained in http://publib.boulder.ibm.com/infoce.../r0004239.html

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Alternatively, you can set the DECLARE inside its own BEGIN ... END scope:
    Code:
    BEGIN
       DECLARE ...
       DECLARE ...
    
       SET ...
    
       BEGIN
          DECLARE ...
    
          OPEN ...
       END
    
    END
    One thing that strikes me as a bit odd is that you set "p_mem" first. You could directly use "mem_name" in the DECLARE CURSOR statement and avoid the SET. Another thing is that you have an OUT parameter "lead_name", but that parameter is not set at all in the stored procedure. You may want to set it to some explicit value to have well-defined behavior.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Mar 2012
    Posts
    8

    Smile problem resolved

    Thanx for your help everyone....problem resolved.

Posting Permissions

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