Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    12

    Unanswered: Select Statement Inside of a Call Statement

    I have a process in Java that's essentially replicating data for the user. They want to work off a copy of something so we push the data into Java then send it back to the DB to be entered as a new record.

    To increase performance, I'd like to do this on the DB side through a stored procedure. I'm just duplicating a selected record (across several tables)

    Basically, a whole bunch of ADD stored procs are running to add bits and pieces of the replicated data back to the database.

    Through my new stored procedure, I'd like to call the little stored procedures so that the little ones can be updated independently of this master one I'm writing. The problem I'm running into is that I know what data I want to select from the database to replicate but DB2 won't let me pass the data into the call statement for the small stored procedures.

    This is what I want to accomplish:

    db.storedproc has three parameters so I want to do this:

    call db.storedproc((select r.data1,r.data2 from table r where r.id = iIncomingID),extrafield);

    When I compile, I get "Multiple columns are returned from a subquery that is allowed only one column.. SQLCODE=-412, SQLSTATE=42823, DRIVER=3.62.80
    Multiple columns are returned from a subquery that is allowed only one column.. SQLCODE=-412, SQLSTATE=42823, DRIVER=3.62.80"

    I did some research and found that yes, I can do a sub-query for a parameter but it has to be one parameter at a time. (one column return into one parameter which makes sense)

    Some of my tables are big. I don't want to write a select statement for each parameter in my call statement.

    What would be the best way to work around this? Is there a way to tell the db that each column in a select statement (returning only one row) is a parameter for the stored procedure I'm trying to call? Do I have to declare each of my parameters as variables, run a select statement using INTO to populate them, then call my internal stored proc using the variables?

    I'm just looking for a way to call stored procedures within a stored procedure using data(multiple columns) pulled from a select statement.

    Any help or suggestions would be great, thanks!

    DB2 Workgroup Edition - 9.7 Fix Pack 4
    Last edited by ethansmith; 04-08-12 at 11:38.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by ethansmith View Post
    Do I have to declare each of my parameters as variables, run a select statement using INTO to populate them, then call my internal stored proc using the variables?
    Yes.

    Andy

Posting Permissions

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