Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    59

    Unanswered: alter table statement in Stored procedure

    Hi,
    I went through a earlier thread regarding putting a alter statement inside a SQL stored procedure.
    http://dbforums.com/showthread.php?threadid=698613

    declare v_stmt varchar(20);
    now store ur query inside this variable,then prepare the statement.

    v_stmt = 'alter table emp drop primary key';
    prepare S1 from v_stmt;
    EXECUTE IMMEDIATE S1;



    but when i try to build it using stored procedure builder it gives an error saying S1 is not valid in the context it is used. on line " EXECUTE IMMEDIATE S1 "

    apparently "EXECUTE S1 " works just fine


    DB2 v 7.2
    Windows 2K.

    Am i missing something here.

    cheers
    brat.
    Last edited by brat4; 09-18-03 at 07:58.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Brat,

    The way I read the manual, the EXECUTE IMMEDIATE and EXECUTE are two very different statements. The syntax diagrams:

    >>-EXECUTE--statement-name-------------------------------------->

    >--+-----------------------------------+-----------------------><
    | .-,-------------. |
    | V | |
    +-USING----host-variable-+----------+
    '-USING DESCRIPTOR--descriptor-name-'


    and...

    >>-EXECUTE IMMEDIATE--host-variable----------------------------><


    EXECUTE will run a statement that is described by a statement name that was PREPAREd.

    EXECUTE IMMEDIATE executes the statement that is contained by the host variable.

    Using your example, you could wite it like either way below:
    1)
    v_stmt = 'alter table emp drop primary key';
    prepare S1 from v_stmt;
    EXECUTE S1;

    2)
    v_stmt = 'alter table emp drop primary key';
    EXECUTE IMMEDIATE v_stmt;

    Now if you had parameter markers in the statement that you wish to execute, then you can only use EXECUTE.

    HTH

    Andy

  3. #3
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    59
    Yup now i see it, thanks a lot. Appreciate the help.

    cheers
    brat.

Posting Permissions

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