Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: Possible to write a procedure without defining 'CREATE'?

    Hi,

    In MS SQL 2008 it seems to be possible to write a 'procedure' without actually defining the "create procedure". Is this possible to do in DB2 9.7 fixpack 4? So I woul just have an ordinary .sql file that is run e.g. as a transaction within BEGIN/END? If I take away the procedure declaration, my editor refuses to run the .sql file.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    DB2 on LUW lets you (for legacy support only) use uncatalogued stored procedures but those are usually written in a 3-gl like C or similar. Don't go there, unless you like pain.


    DB2 v9.7 has support for anonymous blocks in PL/SQL if you are more familiar with Oracle style , but there are limitations.

    DB2 v9.7 also support local procedures in SQL PL which have specific uses.
    See
    https://www.ibm.com/developerworks/m...mber_115192136

  3. #3
    Join Date
    Aug 2012
    Posts
    24
    I am using DB2 9.7.

    Hmm just cannot get it to work. So I have an sql script with only declarations and selects. I encapsulate the procedure with BEGIN/END. It gives me the error code:

    DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=<ddl-statement>, DRIVER=3.63.108

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Mostly likely you are using invalid syntax.
    If you don't publish your code how do you expect to get help?

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    If you don't publish your code how do you expect to get help?
    Bummer, and i thought today was Psychic Day - when these things could be known without posting . . .

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

  7. #7
    Join Date
    Aug 2012
    Posts
    24
    One statement that I am trying to execute is the one below:


    BEGIN
    DECLARE V_SQL VARCHAR(1024);
    SET V_SQL = ('BEGIN
    IF EXISTS(SELECT NAME FROM SYSIBM.SYSTRIGGERS WHERE NAME = ''TRIGGER_EMPLOYEE_FOR_DELETES'') THEN
    DROP TRIGGER TRIGGER_EMPLOYEE_FOR_DELETES;
    END IF;
    END;');

    PREPARE S1 FROM V_SQL;
    EXECUTE S1;
    END

    I have tried adding/removing ";" and statement symbol "!" but still cannot get it to work.

Posting Permissions

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