Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Unanswered: having problem implementing stored procedure

    hi!! i'm trying to implement a stored procedure but somehow it wont work >< i think i followed the syntax correctly...

    Code:
    create procedure update_discount(in discount decimal(4,2))
    language sql
    begin
    update Sales set Sales_Amount=Sales_Amount*discount where 
    Sales_Quantity >=2
    end
    i create this in the command line of ibm db2...i read some other forums and websites saying we have to create a cursor? is it necessary?

    thank you

    regards,
    shinichi90

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... but somehow it wont work
    How did you know it wont work?

    Did you got some messages?
    If so, when(complie time? or execution time?) and what message(including code and full message text) did you received?

    At least, please try to add semicolon, like this:
    update Sales set Sales_Amount=Sales_Amount*discount where
    Sales_Quantity >=2;

  3. #3
    Join Date
    Jul 2010
    Posts
    8
    it could not compile...i already tried putting semicolon after the query..
    the error was..

    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 "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    thank you

    regards,
    shinichi90

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    maybe you should build your stored procedure using command editor. input "db2ce" to open

  5. #5
    Join Date
    Jul 2010
    Posts
    8
    Quote Originally Posted by wilsonfv View Post
    maybe you should build your stored procedure using command editor. input "db2ce" to open
    sorry i did it in command editor not command line...>< sorry my bad...

  6. #6
    Join Date
    Apr 2009
    Posts
    42
    maybe you should build your stored procedure using command editor. input "db2ce" to open

  7. #7
    Join Date
    Apr 2009
    Posts
    42
    if you are using command editor, add @ at the end of your procedure.

  8. #8
    Join Date
    Jul 2010
    Posts
    8
    Quote Originally Posted by wilsonfv View Post
    if you are using command editor, add @ at the end of your procedure.
    Code:
    create procedure procedure_name()
    language sql
    begin
    //sql code
    end@
    like this? it came out the same error....>< what does this '@' stands for?

    regards,
    shinichi90

  9. #9
    Join Date
    Apr 2009
    Posts
    42
    Code:
    create procedure procedure_name()
    language sql
    begin
    
    sql code1;
    
    sql code2;
    
    end@
    First, add @ at the end of your procedure.
    Second, you should see an input field right at the bottom of the command editor named "Statement termination character".
    Means if you set @ as "Statement termination character", the editor once see the @, it will know this is the end of your procedure.

  10. #10
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Try :
    create procedure update_discount(in discount decimal(4,2))
    language sql
    begin
    update Sales set Sales_Amount=Sales_Amount*discount where
    Sales_Quantity >=2;
    end@
    TERMINATE@



    then place in a file - and run from an interface - such as Putty - as :

    db2 -td@ -f myfilename.sql

Posting Permissions

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