How to use 'Execute Procedure' to retrieve table data from an ext. MS SQL ?

[Former Member]

Hi,

We have to call a stored procedure from an external MS SQL database . We are able to connect to the database and are able to successfully do a SQL (Select ) on the SQL tables. However to maintain data integrity and other security issues, we have to call a stored procedure on that database from SAP. The stored rocedure will return a table which will contain multiple records .

We have tried using the EXECUTE PROCEDURE command. However it returns SQL errors?

Our SAP instance is on MS SQL.

Please note we have gone through all SAP notes and SDN already. The query is not about connecting to the database. It is solely to find out the correct way for calling the stored procedure. The example in the SAP help documentation is for single IN and OUT parameters. We are able to do that successfully. Our requirement is to get a table back from the stored procedure.

Please help with the correct syntax and code example if you have one.

I would be glad to provide any other information that may be helpful.

Thanks in advance.

[Former Member]

Hi,

We have to call a stored procedure from an external MS SQL database . We are able to connect to the database and are able to successfully do a SQL (Select ) on the SQL tables. However to maintain data integrity and other security issues, we have to call a stored procedure on that database from SAP. The stored rocedure will return a table which will contain multiple records .

We have tried using the EXECUTE PROCEDURE command. However it returns SQL errors?

Our SAP instance is on MS SQL.

Please note we have gone through all SAP notes and SDN already. The query is not about connecting to the database. It is solely to find out the correct way for calling the stored procedure. The example in the SAP help documentation is for single IN and OUT parameters. We are able to do that successfully. Our requirement is to get a table back from the stored procedure.

Please help with the correct syntax and code example if you have one.

I would be glad to provide any other information that may be helpful.

Thanks in advance.

[Former Member]

Did you go through this?

[Using ABAP to access non-SAP databases|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/11543] [original link is broken] [original link is broken] [original link is broken];

And

[Installing DB Connect with MSSQL Server Database|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/4305] [original link is broken] [original link is broken] [original link is broken];?

[Former Member]

Hi Amit,

Thanks for the suggestions. However my question is specifically about using the stored procedure. As I have mentioned, I am able to retrieve data using the select from tables. I would like to use the EXECUTE PROCEDURE command. My code would look something like this

data:

someInt type i value 5,

• someotherInt type i,

begin of fs_table,

a type int2,

b type int2,

end of fs_table,

t_table like standard table of fs_table with header line.

DATA: exc_ref TYPE REF TO cx_sy_native_sql_error,

error_text TYPE string.

*Test if connection 'FAST_DEV' has already been opened

EXEC SQL.

SET CONNECTION :'FAST_DEV'

ENDEXEC.

IF SY-SUBRC <> 0.

Write:/ 'Connection not yet opened.'.

EXEC SQL .

CONNECT TO :'FAST_DEV'

ENDEXEC.

IF SY-SUBRC <> 0.

• error handling

else.

write:/ 'Connection to FAST_DEV opened.'.

ENDIF.

endif.

EXEC SQL.

execute procedure testSAP_sayhi2 ( out :someInt, out :t_table )

ENDEXEC.

I am trying to get data into t_table from the procedure. However I get all sorts of SQL errors with no good description.

[nielsjorgen_trans]

Hi Nishikant.

Just to let you know, that we are experiencing exactly the same problem reading MSSQL Stored Procedures from SAP as you. We are able to read from single tables, but it just doesn't seem to work if we use stored procedures.

We have created the following and very simple stored procedure in MSSQL

Create procedure tt_NJT @Kundenr VARCHAR(20)

as

SELECT Ordrenr FROM FSHeader where Kundenr = @Kundenr

But when calling it from SAP using the following code:

EXEC SQL.

EXECUTE PROCEDURE tt_NJT ( IN '0000011050', OUT :ordrenr )

EXEC SQL.

it dumps with the errormessage: "Error 8144 occurred in the current database connection "FSDATA - Procedure tt_NJT has to many arguments specified"

As you can see there is only one single IN and one single OUT-parameter, so we don't really understand what is wrong.

However if I change the OUT-parameter to an INOUT-parameter like shown below

no dumps and no error-message are returned, but it still doesn¨'t return any values to my the :ordrenr variable.

EXEC SQL.

EXECUTE PROCEDURE tt_NJT ( IN '0000011050', INOUT :ordrenr )

EXEC SQL.

We would also be happy if anybody could help solving this problem.

Kind regards.

Niels Trans.

[Former Member]

Hi,

I too have a similar issue, I have a stored procedure written in MS SQL and it executes but I need the result in an internal table; How do I get it into an internal table?

Following code is being used. Now the output of the query that is being executed, I need it in my internal table

EXEC SQL.
  EXECUTE PROCEDURE d01.Z_PRICINGREPORT ( IN :i_mandt,
                                          IN :i_kschl,
                                          IN :i_vkorg,
                                          IN :i_vtweg,
                                          IN :i_matkll,
                                          IN :i_matklh,
                                          IN :i_matnrl,
                                          IN :i_matnrh,
                                          IN :i_inco1,
                                          IN :i_inco2,
                                          IN :i_zsalel,
                                          IN :i_zsaleh,
                                          IN :i_kunnrl,
                                          IN :i_kunnrh,
                                          IN :i_chargl,
                                          IN :i_chargh,
                                          IN :i_vkburl,
                                          IN :i_vkburh,
                                          IN :i_vkgrpl,
                                          IN :i_vkgrph,
                                          IN :i_wrkstl,
                                          IN :i_wrksth,
                                          IN :i_mtf,
                                          IN :i_mtt,
                                          IN :i_datbi,
                                          IN :i_werks,
                                          IN :i_stock
  )



ENDEXEC.
CATCH cx_sy_native_sql_error.
    MESSAGE `Error in procedure handling` TYPE 'I'.
endtry.

Warm Regards,

Abdullah

[Former Member]

I too have a similar issue . I am reading MSSQL Stored Procedures from SAP. I have mentioned both Input and Output parameters in the SQL statement.

Passing both input and  output parameters. When i execute below statement getting the short dump : SQL error 8144 occurred when executing Native SQL.Procedure or function  has too many arguments specified".

  EXECUTE PROCEDURE < procedurename> 

( IN :ls_in_ebeln,
  IN :ls_in_ebelp,
OUT :ls_out-ebeln,
OUT :ls_out-ebelp,
OUT :ls_out-qty  )

[Tenorio]

Stored procedures can be executed using the following instance method of the class CL_SQL_STATEMENT

EXECUTE_PROCEDURE

The method has an mandatory input parameter PROC_NAME of type string, which must be passed the name of an existing stored procedure statement. In the same way as in DML statements, it is possible to bind ABAP data objects as actual parameters to the formal parameters of the stored procedure using the method SET_PARAM. The type of parameter must be specified using the additional parameter INOUT. Possible values are defined in the constants C_PARAM_IN, C_PARAM_OUT, and C_PARAM_INOUT of the class CL_SQL_STATEMENT. C_PARAM_IN is the default value. The order of the calls determines the assignment to the formal parameters from left to right.