Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2012
    Posts
    17

    Unanswered: Calling a stored procedure

    Is it possible to call a stored procedure from a UDF that returns a table? Would appreciate if you can give some sample code or examples. Thank you.

    Satish

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by vskr72 View Post
    Is it possible to call a stored procedure from a UDF that returns a table? Would appreciate if you can give some sample code or examples. Thank you.

    Satish
    Yes, if SP didn't make any update, delete or insert...

  3. #3
    Join Date
    Apr 2012
    Posts
    17
    Thanks Lenny. Can you pls see what is the issue here. Here I call the function, I get blank data.

    Code:
    CREATE PROCEDURE repeat_stmt()
    dynamic result sets 1
    LANGUAGE SQL
    BEGIN
      DECLARE c1 CURSOR with return FOR 
        SELECT empno,firstnme FROM employee;
      OPEN c1;
    END
    
    
    CREATE FUNCTION test ()
    RETURNS table (emp_id VARCHAR(35), emp_name VARCHAR(35))
    LANGUAGE SQL 
    MODIFIES SQL DATA
    BEGIN ATOMIC
    declare i_emp_id varchar(35);
    declare i_emp_name varchar(35);
    call repeat_stmt();
    return values (i_emp_id, i_emp_name);
    END

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think you can "Return" the result set from the stored procedure from a function.

    Andy

  5. #5
    Join Date
    Apr 2012
    Posts
    17
    Thank you Andy. So, is there any other alternative for this. my requirement is to write a UDF that returns a table and which will use dynamic SQL. Since I thought that is not feasible, I thought, it might be better to create a SP and call it from a function. Pls advice.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    Andy

  7. #7
    Join Date
    Apr 2012
    Posts
    17
    We use DB2 on LINUXZ64 9.5.7.

    Satish

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If the Stored Procedure filled in a table with the data you want to return, then have the UDF call the Stored Procedure then return a select on this table.

    Andy

  9. #9
    Join Date
    Apr 2012
    Posts
    17
    Actually the stored procedure populates a GTT and I have a cursor open on that (with Dynamic Results set 1). When I run the SP independently, I see the output in STDOUT. Now, to implement what you said - to return a select on this table (from a UDF) - will it work if the stored proc is populating a GTT.

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by vskr72 View Post
    Actually the stored procedure populates a GTT and I have a cursor open on that (with Dynamic Results set 1). When I run the SP independently, I see the output in STDOUT. Now, to implement what you said - to return a select on this table (from a UDF) - will it work if the stored proc is populating a GTT.
    You didn't create your function in right way, as well as return cursor from the SP.

  11. #11
    Join Date
    Apr 2012
    Posts
    17
    Can you pls suggest the best way to implement it? That will really help me.

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink

    Quote Originally Posted by vskr72 View Post
    Can you pls suggest the best way to implement it? That will really help me.
    You can read it everywhere, for example:
    http://publib.boulder.ibm.com/epubs/pdf/dsnsqm04.pdf

Posting Permissions

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