Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    India
    Posts
    3

    Lightbulb Unanswered: How to Create Stored Procedure

    Hi ,
    When I am trying to create the STored Procedure I am facing the problem as follows

    Expected tokens may include: "<psm_semicolon>".sqlstate=42601

    Could any one tell me how to create and parameters I need to set before creating the Stored Procedures.

    Could u explain me briefly...


    It is very urgent............

  2. #2
    Join Date
    Oct 2002
    Posts
    11

    Re: How to Create Stored Procedure

    Hi,

    To issue a CREATE PROCEDURE statement as a DB2 Command Line Processor (DB2 CLP) script, you must use an alternate terminating character for SQL statements in the script. The semicolon (';') character, the default for DB2 CLP scripts, terminates SQL statements within the SQL procedure body.

    To use an alternate terminating character in DB2 CLP scripts, select a character that is not used in standard SQL statements. In the following example, the at sign ('@') is used as the terminating character for a DB2 CLP script named script.db2:

    CREATE PROCEDURE UPDATE_SALARY_IF
    (IN employee_number CHAR(6), IN rating SMALLINT)
    LANGUAGE SQL
    BEGIN
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE EXIT HANDLER FOR not_found
    SIGNAL SQLSTATE '20000' SET MESSAGE_TEXT = 'Employee not found';

    IF (rating = 1)
    THEN UPDATE employee
    SET salary = salary * 1.10, bonus = 1000
    WHERE empno = employee_number;
    ELSEIF (rating = 2)
    THEN UPDATE employee
    SET salary = salary * 1.05, bonus = 500
    WHERE empno = employee_number;
    ELSE UPDATE employee
    SET salary = salary * 1.03, bonus = 0
    WHERE empno = employee_number;
    END IF;
    END
    @

    Hope this helps you.......

    HTH,

    Ashtyu


    Originally posted by babjikolla
    Hi ,
    When I am trying to create the STored Procedure I am facing the problem as follows

    Expected tokens may include: "<psm_semicolon>".sqlstate=42601

    Could any one tell me how to create and parameters I need to set before creating the Stored Procedures.

    Could u explain me briefly...


    It is very urgent............

  3. #3
    Join Date
    Oct 2002
    Location
    India
    Posts
    3

    Question Re: How to Create Stored Procedure

    Hi
    Thanks for reponse.But still Iam facing same problem is there any way or am i need to set any other parameters to run the stored procedures in db2.Could u explain me.
    Thanks
    Babji Kolla
    Hi,

    To issue a CREATE PROCEDURE statement as a DB2 Command Line Processor (DB2 CLP) script, you must use an alternate terminating character for SQL statements in the script. The semicolon (';') character, the default for DB2 CLP scripts, terminates SQL statements within the SQL procedure body.

    To use an alternate terminating character in DB2 CLP scripts, select a character that is not used in standard SQL statements. In the following example, the at sign ('@') is used as the terminating character for a DB2 CLP script named script.db2:

    CREATE PROCEDURE UPDATE_SALARY_IF
    (IN employee_number CHAR(6), IN rating SMALLINT)
    LANGUAGE SQL
    BEGIN
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE EXIT HANDLER FOR not_found
    SIGNAL SQLSTATE '20000' SET MESSAGE_TEXT = 'Employee not found';

    IF (rating = 1)
    THEN UPDATE employee
    SET salary = salary * 1.10, bonus = 1000
    WHERE empno = employee_number;
    ELSEIF (rating = 2)
    THEN UPDATE employee
    SET salary = salary * 1.05, bonus = 500
    WHERE empno = employee_number;
    ELSE UPDATE employee
    SET salary = salary * 1.03, bonus = 0
    WHERE empno = employee_number;
    END IF;
    END
    @

    Hope this helps you.......

    HTH,

    Ashtyu [/SIZE][/QUOTE]

  4. #4
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: How to Create Stored Procedure

    Originally posted by babjikolla
    Hi ,
    When I am trying to create the STored Procedure I am facing the problem as follows

    Expected tokens may include: "<psm_semicolon>".sqlstate=42601

    Could any one tell me how to create and parameters I need to set before creating the Stored Procedures.

    Could u explain me briefly...


    It is very urgent............
    use something like:

    -td@ and -f:

    db2 -td@ -fyourfilename.sql

  5. #5
    Join Date
    Oct 2002
    Location
    India
    Posts
    3
    HI,
    i AM GETTING ERROR WHEN I AM TRYING TO WORK FROM COMMAND LINE.

    eRROR IS -- DB21061E Command line environment not initialized.

    THANKS & REGARDS
    BABJI KOLLA

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Windows ?

    You'd have started the command window using 'cmd' .... That won't work ... You'll have to use db2cmd ...


    Originally posted by babjikolla
    HI,
    i AM GETTING ERROR WHEN I AM TRYING TO WORK FROM COMMAND LINE.

    eRROR IS -- DB21061E Command line environment not initialized.

    THANKS & REGARDS
    BABJI KOLLA

Posting Permissions

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