Copy SP output into Temp table

Help Procedural SQL
1

Hi Guys,

Is it possible to store the SP result into a table? Let me know your thoughts.

e.g. INSERT INTO tmptable(Id) CALL (101);

2

You can’t do it exactly like that, but you can do something like this:

varQTV = p(…);
insert into tmptable(ld) select ld from varQTV;

where varQTV is a query type variable.

Or you can modify your stored procedure to put its output into a table and select it from there.

3

What is your SP doing?

4

Hey :wave: I’ve got a similar question to this – not sure how to fix my code so that it works.

  • I have a stored procedure, called a_procedure, which takes 4 input parameters; ID and three other fixed intergers
  • The procedure returns 3 columns; ID, another_ID and a float

Is it possible to loop over the ID parameter and collect the output of the stored procedure and store it in a table?

Here is what I have so far:

CREATE TEMPORARY TABLE IF NOT EXISTS results_table ( id VARCHAR(255)
        , another_id VARCHAR(255)
        , something FLOAT);

DELIMITER //
CREATE OR REPLACE PROCEDURE p() AS
DECLARE  
  sample_ids QUERY(id VARCHAR(255)) = SELECT id FROM tbl ;
  arr ARRAY(RECORD(id VARCHAR(255)));

BEGIN
  arr = COLLECT(sample_ids);
  FOR x in arr LOOP
  CALL a_procedure(x.id, 10, 10, 10); --- I suspect I need to assign the results to something? Not sure how to access elements 
  INSERT INTO results_table ; --- Not sure how to properly complete this line of code either 
  END LOOP;
END //
DELIMITER ;

CALL p();

SELECT * FROM results_table ;

Many thanks for the help in advance! Sorry to drag up a thread from 2 years ago!!

5

if a_procedure returns one row, you can have it return a RECORD, then reference the record in your INSERT statement following the call to a_procedure.

If a_procedure returns more than one row, you can have it return either:

  • a query type value
  • a json array of documents
  • an array of records
  • just put the results in a temp table (known to just be used on this one session).

However you pass the results back, you then insert them into results_table.
Try to avoid looping with one iteration per row if you can – prefer INSERT INTO … SELECT FROM … if you can make it work.

You can call a procedure p like this:

return_var = p(…args…);

Here, the type of return_var has to match the return type of p.

1 Like
6

See also INSERT_ALL which can insert the contents of an array of records to a table. It is still not going to be as fast as INSERT … SELECT * from a_query_type_value, most likely, but it is a useful shorthand and gives a performance gain compared to doing singleton INSERTs in a loop.

1 Like
7

Thanks so much for the speedy response!

I’m beginning to think there is more I can do with a_procedure itself, rather than calling another procedure. Here is what the a_procedure looks like:

DELIMITER //
CREATE OR REPLACE PROCEDURE `a_procedure` (
    ID varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL 
    , n1 
    , n2 
    , n3 
    ) AS

    DECLARE
    tbl QUERY(ID VARCHAR(128), another_id VARCHAR(36), something FLOAT) = {querying some tables};
    BEGIN
        ECHO SELECT ID, another_id, something FROM art_rec_tbl;
    END //
DELIMITER ;

… and that is it… The Select statement will be greater than one row in most cases, where do you think it makes sense to put the insert statement?

9

If you’re okay putting the INSERT inside of a_procedure, you could just do INSERT…SELECT… inside there. You may not even need the query type value. Just use INSERT…SELECT inline in the code.

10

Hi again @hanson :wave:

Thanks so much! Adding the insert statement does the trick. Although I’ve encountered a new problem, now when calling my procedure through the S2 API I get a 500 error response.

Calling the stored procedure on Singlestore works fine; results are returned as expected and rows are inserted into the intended table. But making the same call through the API fails (whereas it succeeds without the insert statment).

My procedure looks something like this now:

CREATE TABLE api_request_log (
...
);

DELIMITER //
CREATE OR REPLACE PROCEDURE `a_procedure` (
    ID varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL 
    , n1 INT
    , n2 INT
    , n3 INT
    ) AS
DECLARE
    tbl QUERY(ID VARCHAR(128), another_id VARCHAR(36), something FLOAT) = {querying some tables};
    BEGIN
        INSERT INTO api_request_log SELECT ID, another_id, something, NOW()  FROM tbl ;
        ECHO SELECT ID, another_id, something FROM tbl ;
    END //
DELIMITER ;

Any clues as to what might be going on?

11

Please clarify what API you are using. You mean the “Data API”, The ODBC API, or something else? How are you calling the SP from the API and how exactly does the error come back and exactly what does it say?