Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Question Unanswered: stored procedure in stored procedure, trying to return output params

    I'm trying to create a parent type stored procedure that houses a number of other sprocs. Some of the child sprocs contain inserts and have 1 or more (usually 1) output params.

    What's the best way to evaluate these params within the parent sproc?

    The zero below is the placeholder for the output param. Not sure if this is necessary, but it's what I've done in the past. I've already declared any variables that are listed in the code, but I didn't include that part here.

    Inside the parent sproc I'm trying these stmts, all to no avail.
    Code:
    CALL DBname.Sproc1(0) into iVariable ;  <-- this doesn't work.
    Set iVariable = (CALL DBname.Sproc1(0) into iUserID);  <-- this doesn't work.
    Set iVariable = (CALL DBname.Sproc1(0) into iUserID;);  <-- this doesn't work.
    
    if iVariable=1 then
    
    -- DO SOMETHING.
    
    end if;
    If I bring only 1 output param back, what's the best way to handle (and one that works!). What's needed if 2 or more are brought back? Temporary table?

    Can transactions be handled at the parent level, or will each sproc be it's own entity, handling inserts within themselves. What I mean by this, if there's an insert stmt in a child and I don't commit here, can I commit in the parent instead?

    Any help is appreciated! Thanks!

    SkydiverMN

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm not sure where you've seen that funny syntax, CALL .. INTO .., but it's not DB2. You cannot use literals for output parameters: how do you expect a value to be assigned to a literal? The output parameters must be host variables, and CALL SPROC1(iVariable) should do the trick. Also, if you need to return one and only one value you may consider using functions instead.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Posts
    8

    Question Funny syntax...

    Yeah, I don't know if that would be valid in anything, but I was seeing if anything would work. It just happened to be at the top during my cut and paste.

    I'll try the function method, that seems most appropriate to returning a value. I'll let you know.

    Thanks,
    SkydiverMN

  4. #4
    Join Date
    Aug 2009
    Location
    Россия
    Posts
    1

    знакомства для секса


  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Usually Call(i1, i2, ..., iK, io1, io2, ..., ioN, o1, o2, ..., oM)....

    You can return from SP any number of the result sets....

    But Call is Call.... You can't insert the values into the variables, using SP call.

    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by skydiverMN
    I'm trying to create a parent type stored procedure that houses a number of other sprocs. Some of the child sprocs contain inserts and have 1 or more (usually 1) output params.

    What's the best way to evaluate these params within the parent sproc?

    The zero below is the placeholder for the output param. Not sure if this is necessary, but it's what I've done in the past. I've already declared any variables that are listed in the code, but I didn't include that part here.

    Inside the parent sproc I'm trying these stmts, all to no avail.
    Code:
    CALL DBname.Sproc1(0) into iVariable ;  <-- this doesn't work.
    Set iVariable = (CALL DBname.Sproc1(0) into iUserID);  <-- this doesn't work.
    Set iVariable = (CALL DBname.Sproc1(0) into iUserID;);  <-- this doesn't work.
    
    if iVariable=1 then
    
    -- DO SOMETHING.
    
    end if;
    If I bring only 1 output param back, what's the best way to handle (and one that works!). What's needed if 2 or more are brought back? Temporary table?

    Can transactions be handled at the parent level, or will each sproc be it's own entity, handling inserts within themselves. What I mean by this, if there's an insert stmt in a child and I don't commit here, can I commit in the parent instead?

    Any help is appreciated! Thanks!

    SkydiverMN
    Maybe you mean the UDF (function), not SP ?

    You can use 2 types of UDF: Scalar (which returns one value) and Table Function which returns the DB2 table.

    Lenny

Posting Permissions

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