Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    19

    Unanswered: How to write stored procedure

    Hi guys,
    I am using DB2 9.7 Control Center..
    I dont need the syntax of Stored procedure.
    but a way(method) to create it ..?

    i have foll SP :
    "Create Procedure ABC(IN dno)
    Language SQL
    BEGIN
    Select * From sagar3.employee where EMPDEPT=dno;
    END"

    but when i execute this i get 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:
    SQL0104N An unexpected token "*" was found following "age SQL BEGIN Select".
    Expected tokens may include: "(". LINE NUMBER=4. SQLSTATE=42601"


    can anyone tell me how do i create and call my stored proc ?

    i know i should call it by "CALL ABC(10);" , but need the steps to execute it properly.
    PLEASE HELP ME EXECUTING MY FIRST STORED PROCEDURE IN DB2.

    THAKY YOU !!

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    look at the samples shipped with db2 in sqllib/samples....
    the into is missing in the select ..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use Data Studio to develop and debug stored procedures

    --
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2013
    Posts
    19
    THANKS FOR THE REPLY GUYS

    przytula_guy You rock

    I went through those samples

    and the foll code WORKED perfectly fine for me..



    CREATE PROCEDURE abc1 (IN dno integer,OUT answer integer)
    LANGUAGE SQL
    BEGIN
    SELECT EMPNO INTO answer FROM sagar3.employee where EMPDEPT=dno;
    END@

    CALL abc1(10,?)@

    I just ran my 1st STORED PROC in db2

  5. #5
    Join Date
    Mar 2013
    Posts
    19
    Quote Originally Posted by sathyaram_s View Post
    You can use Data Studio to develop and debug stored procedures

    --
    actually i wanted it for office work
    we use DB2 CC here (Control Center) ..
    ANyways thanks mate

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Glad you were able to create the procedure

    IBM Data Studio - IBM Data Studio - United Kingdom

    Data Studio is the IBM replacement for Control Center and related tools. For serious stored procedure development, you need a tool like Data Studio. Control Center/Command editor are good for quick/short procedures.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2013
    Posts
    19
    Quote Originally Posted by sathyaram_s View Post
    Glad you were able to create the procedure

    IBM Data Studio - IBM Data Studio - United Kingdom

    Data Studio is the IBM replacement for Control Center and related tools. For serious stored procedure development, you need a tool like Data Studio. Control Center/Command editor are good for quick/short procedures.

    THanks for the info !
    wasnt aware abt it

Tags for this Thread

Posting Permissions

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