/scripts/ahub.form.attachments.js
0

Insert Statement from Call StoredProcedure

Feb 24, 2017 at 02:58 PM

61

avatar image
Former Member

Hi,

I need to insert records in a table getting its values from calling a stored procedure.

For example:

insert into "TESTTABLE" Values ("Code", "Name") call "TEST_SP" ();

// TEST_SP returns two - columns records which I want to insert in "TESTTABLE".

If I run it like above I get:

SAP DBTech JDBC: [7]: feature not supported

I also tried

INSERT INTO #tmp
execute test_proc

(which works fine on MS_SQL Server) but it doesn't work!

Thank you in advance,

Dimitris

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Feb 24, 2017 at 08:20 PM
3

Using the procedure directly in an Insert statement is not possible to my knowlegde. But you have different options to handle that situation.

Use "with overview" option for call procedure statement in case the result structure of your procedure matches the target table
In case that the result structure of the procedure output parameter matches the target table, you can use the "with overview" option for the call procedure statement. When you use that and pass the table name of your target table to the output parameter, the data is directly inserted in that table.

Example (assuming having a target table "MISC"."TARGET_TABLE"):

CALL "MISC"."MY_PROCEDURE"( et_result => "MISC"."TARGET_TABLE") WITH OVERVIEW;

In case the procedure result structure does not match the structure of the target table you have several options. Following a few are described briefly.

Create a wrapper procedure
Create a procedure which wraps your code (call your procedure and store the result in a table variable; insert the data into the target table).

Create a table function which wraps the call of your procedure
You can create a table function which wraps the call of your procedure and returns the result of the procedure. The table function then can be called using a select used for the insert statement.

INSERT INTO "MISC"."TARGET_TABLE" (COL1, COL2) ( SELECT COL1, COL2 FROM "MISC"."WRAPPER_TABLE_FUNCTION"() );

Use a temporary table
As long as you are in the same session you can use a local temporary table store the result of the procedure. The data then can be selected from the temporary table during Insert statement execution.

DROP TABLE #TMP_TAB;
CREATE LOCAL TEMPORARY TABLE #TMP_TAB ( COL1 integer, COL2 integer);
CALL PROCEDURE "MISC"."MY_PROCEDURE" ( et_result => #TMP_TAB) WITH OVERVIEW;
INSERT INTO "MISC"."TARGET_TABLE" (COL1, COL2) ( SELECT COL1, COL2 FROM #TMP_TAB);

You can also avoid creating the temporary table manually in case you pass a NULL value to the output parameter of the procedure. In that case automatically a tempoary table is created. The temporary table name is returned as result from the procedure. You just have to determine the temporary table name and get the data you need for the insert.

Regards,
Florian

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Dear Florian,

Thank you very much for your answer.

It works fine!

Regards,

Dimitris

0
Skip to Content