topic Re: Call a SQL Stored Procedure in Application Development and Automation Discussions

Call a SQL Stored Procedure

Former Member — Thu, 06 Aug 2009 21:54:11 GMT

Can anyone provide the ABAP code to call a SQL stored procedure and use it's resulting recordset? We want to load the resulting recordset from a stored procedure into a cursor. This stored procedure does not have an output parameter.

Re: Call a SQL Stored Procedure

Former Member — Fri, 07 Aug 2009 04:43:33 GMT

This message was moderated.

Re: Call a SQL Stored Procedure

Former Member — Fri, 07 Aug 2009 04:48:02 GMT

This message was moderated.

Re: Call a SQL Stored Procedure

Former Member — Fri, 07 Aug 2009 04:50:51 GMT

This message was moderated.

Re: Call a SQL Stored Procedure

Former Member — Tue, 11 Aug 2009 19:39:01 GMT

I have received three suggested reading links. One talks about calling a stored procedure in Business Objects. My requirement does not involve Business Objects. Another talks about receiving notification of a data-driven event in Business One. My requirement does not involve a data-driven event, nor are a Business One customer, we are on R/3 4.6C. The third link talks about calling a stored procedure in MaxDb using SAP Xi. My Basis Admin tells me that we do not have MaxDb. So even though I am just a functional analyst -- not an ABAPer -- and I do appreciate these first three suggestions, I do not think these links are going to be of assistance. Does anyone out there have any other suggestions for me.

Re: Call a SQL Stored Procedure

former_member194669 — Tue, 11 Aug 2009 19:53:02 GMT

Pl. check this link , its have details about how to call stored procedure using abap

http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

a®

Re: Call a SQL Stored Procedure

Former Member — Tue, 11 Aug 2009 20:40:45 GMT

The link to the SAP Library Native SQL article and its related Note 44977 are closer to what I need. However, they both talk about returning only one field from one table. We need to return a record set -- multiple records each with multiple fields from one SQL data table. Any additional suggestions are welcomed.

Re: Call a SQL Stored Procedure

JuergenBauer — Fri, 18 Sep 2009 11:27:26 GMT

Hi in Release 4.6C you can do this like in sapnote 176792.:

exec sql performing form.

select * from $PROC$procedure_name where ( : Par1, :par2 ...)

into :out_1, :out:2 .... :Out_x

endexec.

this works, as far as I know only for MSSQL and kernal 640.

Greetings Jürgen.

Unfortunately this doesn't work any more in kernel 701 PL55. So I'm looking myself for a solution.

Re: Call a SQL Stored Procedure

JuergenBauer — Mon, 21 Sep 2009 09:19:37 GMT

Hi found my problem:

there must be a

SET NOCOUNT ON

as first statement in the stored procedure. If not, a Select, insert, update or merge statement in the stored procedure sends a text message like that:

(10 row(s) affected)

This confuses the kernel.

So I can say now Kernel 701 with Patchleve 55 can do this also.

For example you want to call a stored procedure sp_dummy with 2 Parameters this can be done like that:


data:
  PAR1(30) type C,
  PAR2(30) type C,
  OUT1 type I,
  OUT2(30) type C,
  OUT3(30) type C.

exec sql performig sp_return.
  select * from $PROC$sp_dummy 
    where ( :par1, :par2 ) 
    into :out1, :out2, : out3
endexec.

form sp_return.
   * some coding 
endform

Jürgen

Re: Call a SQL Stored Procedure

Former Member — Thu, 24 Sep 2009 16:08:27 GMT

Thank you for the syntax. This worked great for us.