How to execute MS SQL stored procedure with internal table parameter

[hardyp180]

In the Standard SAP documentation for the EXEC SQL - EXECUTE it clearly states that:-

For actual parameters, you can use literals or host variables identified by a colon (:), which could also be internal tables in this case. When internal tables are used they must be standard tables without secondary table keys.

However whatever syntax I try to use I get an error when trying to call the stored procedure. this is a stored procedure in an MS SQL database.

The syntax I am using is:-

EXEC SQL.

EXECUTE PROCEDURE dbo.UpdateDeliveries ( IN :it_output )

ENDEXEC.

(IT_OUTPUT is of course a standard internal table)

That gives no syntax error but an SQL exception is raised at runtime.

My collegaue suggested I try:-

EXECUTE dbo.UpdateDelives @TblDeliveries = :it_output

Which does give a syntax error, saying that IT_OUTPUT cannot be a table.

What is the correct way to call a stored procedure with a table parameter on a MS SQL database from inside ABAP using native SQL?

Cheersy Cheers

Paul

[horst_keller · Accepted solution]

Well, yes, host variables can also be internal tables, but you cannot use them. The possibility of internal tables was introduced for special internal purposes. The next version of that documentation will not mention internal tables any more.

[hardyp180 · Accepted solution]

First of all thank you for getting back to me so fast. I thought you might and as always, I am very grateful for the rapid response.

I suspected that internal tables were not supported as stored parameter procedures, based on the code behaviour, despite the documentation saying otherwise.

Often the biggest difference between cultures is the sense of humour. What seems funny to one person is often meaningless to someone from another country.

I am in Australia, though from the UK by birth. We had a meeting of all the programmers today, and for light relief I put your answer above on the projector. The whole room fell about laughing, and one person almost died, he was laughing so hard he could not breathe.

As I said, you might not see what we found so funny, hopefully you will!

Cheersy Cheers

Paul

[horst_keller]

A Workaround with ADBC that works on the SAP HANA Database:

DATA result TYPE ...
DATA(query_result) = NEW cl_sql_statement(
   )->execute_query( statement = `CALL "..."( '` && sy-mandt && `', NULL )` ).
query_result->set_param_table( itab_ref = REF #( result ) ).
query_result->next_package( ).

According to the HANA SQL documentation:

"When you pass NULL to the output parameters, temporary tables holding the result sets are generated. These tables are dropped automatically once the database session is closed."

The above works, if the called procedure has an input parameter for the client and a tabular output parameter. Interesting enough, the EXECUTE_PROCEDURE method of ADBC does not support internal tables itself.

You might check for MS SQL, if such workarounds can be used there too.