Is there any plan to support Out and InOut parameters in Singlestore stored procedures? We are assessing use of Singlestore DB for replacing multiple existing applications using DB2 and Oracle, which has thousands of stored procedures and majority of them make use of output parameters and have multiple resultsets. This seems to be major roadblock in moving ahead with this migration. So I would like to understand if this feature will be added? We are also considering Cockroach DB which has added full stored procedure support last year.
Yes, we are considering that. Can you give us full details of what you are looking for, and which has top priority, Oracle or DB2? Please work through your account team if you like and they can get it to us.
It is possible to return a value from an SP, e.g. you can do:
x = SPname(args);
to call an SP and get the return value into variable x in an SP or anon code block. And for a multi-value result, you could return a JSON value with multiple properties. I know this is not 100% equivalent to output params but it can help.
To send a return value from an SP to a client program, you can use ECHO. E.g.
We are prioritizing conversion of DB2 on z/OS apps first. The SPs in DB2 have both output parameters and multiple resultsets. Typically we will have 2 output parameters - error code and error description and standard set of parameters. The SPs will have one or more input parameters. On successful execution, SP will return 1 or more resultsets and error code and description will be 0 and Success. But if there was error encountered in SP, we will have some value in error code and description parameters, with no resultset returned. So I dont think echo with return value will work for us as we will need both information - resultsets as well as output parameters.
Only for batch SP use cases, usage of ECHO may work. But batch SPs are very small in number. Majority of the SPs are for online use and called from distributed .Net or Java stack.
We do support multiple results sets by running ECHO SELECT from inside the SP. And you could return a JSON object with the error code and string in two different fields.