Execute Immediate a SP/Func inside SP from a query table

Hello,

I’m having trouble to execute immediate a SP inside a SP from a table.
because i have a lot of SP stored in a table (as a data/string).

in oracle we can use “select sp_1(:1,:2) from dual”
and running it via execute immediate. how’s singlestore do that? i get error.

please help, because we want to migrate from oracle to singlestore. i changed a lot of SP from oracle to singlestore. thanks.

Hi Missgeekcode. :wave: Can you share what service you are running on and the version number?

SingleStore doesn’t support selecting from the output of an SP. But you can return a query type value from an SP, then SELECT from the query type value, into wherever you want to put the result.

https://docs.singlestore.com/managed-service/en/reference/sql-reference/procedural-sql-reference/query.html

Also, you could use a temp table (or a regular table( to save the result at the bottom of one SP and use it in another SP, just referencing it by name.

1 Like

Also, you can use dynamic SQL to execute a SQL CALL statement. You could retrieve the procedure name and arguments and create a CALL statement from those, then execute it. E.g. you can do this:

do declare s text; begin s = "call p()"; execute immediate s; end //

You could write code to retrieve whatever you want, than create a CALL statement in s, and execute it.

1 Like