Topic
6 replies Latest Post - ‏2012-12-14T16:19:00Z by krmilligan
Mauri_Claudio
Mauri_Claudio
19 Posts
ACCEPTED ANSWER

Pinned topic Declaring subroutines within an enclosing stored procedure.

‏2012-11-22T07:07:07Z |
Hi to everybody,

I'm just wondering if declaration of 'subroutines' is supported in DB2 for i. What I mean is something
similar to this:

CREATE PROCEDURE ALFA(....)
LANGUAGE SQL
P1: BEGIN
  • MAIN BODY

CREATE PROCEDURE INNERPROC(....)
END P1.
Updated on 2012-12-14T16:19:00Z at 2012-12-14T16:19:00Z by krmilligan
  • Mauri_Claudio
    Mauri_Claudio
    19 Posts
    ACCEPTED ANSWER

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-11-22T07:21:03Z  in response to Mauri_Claudio
    I'm really sorry, I unintentionally sent this post before completing !
    What i'm asking is if it's possible to write this pseudo-code:

    
    CREATE PROCEDURE ALFA (....) LANGUAGE SQL P1 :BEGIN -- inner the body, declare another stored locally visible CREATE PROCEDURE BETA (...) LANGUAGE SQL P2: BEGIN   END P2; ---- CALL BETA(....);   END P1
    


    Thank you for your help.
  • krmilligan
    krmilligan
    437 Posts
    ACCEPTED ANSWER

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-11-26T19:47:22Z  in response to Mauri_Claudio
    No, that's not possible. You can nest BEGIN END compound statements within a single stored procedure, but not sure how readable that will be.
    • Mauri_Claudio
      Mauri_Claudio
      19 Posts
      ACCEPTED ANSWER

      Re: Declaring subroutines within an enclosing stored procedure.

      ‏2012-11-27T06:44:18Z  in response to krmilligan
      Ok, thanks for your reply and explanation. My question arose since I need a mean to keep together stored procs that are logically dependent on each other. I tought that defining subroutines just inside the body of the "main" stored proc would achieve that.
      Is there anything similar to the concept of "package" in DB2 for i ?

      Thank you again for your support.
      • B.Hauser
        B.Hauser
        245 Posts
        ACCEPTED ANSWER

        Re: Declaring subroutines within an enclosing stored procedure.

        ‏2012-11-27T08:20:47Z  in response to Mauri_Claudio
        Why not simply calling the dependent stored procedures from the first one?

        Birgitta
        • Mauri_Claudio
          Mauri_Claudio
          19 Posts
          ACCEPTED ANSWER

          Re: Declaring subroutines within an enclosing stored procedure.

          ‏2012-11-27T08:35:18Z  in response to B.Hauser
          Hi Birgitta,

          that's of course a feasible solution,and works well. Actually I deploy all stored procedures in a "reserved" schema, and in stored procs I use dynamic SQL to address any other schema / library I may need on my i5 system. But as times goes by, number of stored increases, and, in general, they are all uncorrelated each others. It would be nice if it were possible to group them in "packages". I came from Java world, where using packages is a very neat way to separate classes. I'm searching - if it exists - for a similar mean, but applied to stored.
          • krmilligan
            krmilligan
            437 Posts
            ACCEPTED ANSWER

            Re: Declaring subroutines within an enclosing stored procedure.

            ‏2012-12-14T16:19:00Z  in response to Mauri_Claudio
            DB2 for i does not yet support packages for SQL stored procedures.