Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unanswered: stored proc with table alteration function

    I would like to ask is there any possibility for us to write a stored proc with table alteration statement? I have came across several stored procs but found that most of them are for select or joining tables purposes. Below is the stored proc sql statement:

    CREATE PROCEDURE IPMS.TESTID
    (IN V_TABLE VARCHAR(20))

    SPECIFIC IPMS.SQL040603171458060
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    MODIFIES SQL DATA
    INHERIT SPECIAL REGISTERS

    BEGIN
    DECLARE v_Table1 VARCHAR(20);
    DECLARE V_Max integer;

    SET v_Table1 = V_Table;

    DECLARE id_cursor CURSOR FOR
    SELECT MAX(id) FROM test;
    BEGIN

    OPEN id_cursor;
    FETCH FROM id_cursor INTO V_Max;

    ALTER TABLE IPMS.v_Table1
    LOCKSIZE ROW
    APPEND OFF
    NOT VOLATILE
    ALTER COLUMN ID
    RESTART WITH V_Max;

    CLOSE id_cursor;
    END;

    END;


    I tried to run the above stored proc and get this error:
    [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "ALTER TABLE IPMS.v_Table1 L" was found following "d_cursor INTO V_Max;". Expected tokens may include: "<psm_labellable_stmt>". LINE NUMBER=1. SQLSTATE=42601

    I'm new in compiling stored proc. Can anyone give me some guide on this?

    Thanks in advance.

    Regards.

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Refer to documentation on 'PREPARE' and 'EXECUTE'. This will allow you to dynamically prepare a SQL statement then execute the variable that holds that SQL statement.

Posting Permissions

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