Topic
3 replies Latest Post - ‏2014-06-27T16:05:17Z by CRPence@vnet.ibm.com
msoucy
msoucy
11 Posts
ACCEPTED ANSWER

Pinned topic Returning a single value in a SQL stored procedure

‏2014-06-25T17:16:54Z |

I have a SQL Stored Procedure that accepts 3 input parameters. The SQL stored procedure calls an RPG ILE service program for the logic. I decided that I'd like to return a parameter that states the RPG program processed everything successfully. Do I specify a 4th input parameter with INOUT or OUT as the parameter type or is there a better way to do this? If I specify the parameter as a type OUT how is this specified in my RPG service program?  Is it an input parameter or a return variable?

  • B.Hauser
    B.Hauser
    250 Posts
    ACCEPTED ANSWER

    Re: Returning a single value in a SQL stored procedure

    ‏2014-06-26T05:06:09Z  in response to msoucy

    Several questions:

    1. Is the service program procedure registered directly or did your write some SQL code that calls the RPG service program procedure?
    2. Has the RPG procedure an return value or only input parameters?

    If the RPG procedure is registered directly just add an additional output parameter that returns whatever you want to confirm the RPG procedure is completed successfully. This output parameter must also be added to the stored procedure definition.

    Birgitta

    • msoucy
      msoucy
      11 Posts
      ACCEPTED ANSWER

      Re: Returning a single value in a SQL stored procedure

      ‏2014-06-26T13:17:28Z  in response to B.Hauser

      Hi Birgitta,

      Thank you for your reply. I'm not sure if I understand what you mean by if my procedure is "registered directly".  Attached is a copy of procedure prototype in my RPG program and the SQL create procedure command I'm using.

      Mike
       

      Attachments

  • CRPence@vnet.ibm.com
    31 Posts
    ACCEPTED ANSWER

    Re: Returning a single value in a SQL stored procedure

    ‏2014-06-27T16:05:17Z  in response to msoucy

    Seems to me, that for the described: For success, just as with any SQL [CALL] request, check the SQLCODE and SQLSTATE after the SQL request.  For a failure, the invoked SP should notify of any failure; that failure would be reflected in the SQL diagnostic area.