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?
Pinned topic Returning a single value in a SQL stored procedure
B.Hauser 1000007U1D250 PostsACCEPTED ANSWER
Re: Returning a single value in a SQL stored procedure2014-06-26T05:06:09Z in response to msoucy
- Is the service program procedure registered directly or did your write some SQL code that calls the RPG service program procedure?
- 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.
msoucy 110000H7UN11 PostsACCEPTED ANSWER
Re: Returning a single value in a SQL stored procedure2014-06-26T13:17:28Z in response to B.Hauser
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.
CRPence@vnet.ibm.com 2700018QPM31 PostsACCEPTED ANSWER
Re: Returning a single value in a SQL stored procedure2014-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.