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

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
    255 Posts

    Re: Returning a single value in a SQL stored procedure

    ‏2014-06-26T05:06:09Z  

    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

    Re: Returning a single value in a SQL stored procedure

    ‏2014-06-26T13:17:28Z  
    • B.Hauser
    • ‏2014-06-26T05:06:09Z

    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

    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
    38 Posts

    Re: Returning a single value in a SQL stored procedure

    ‏2014-06-27T16:05:17Z  

    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.