6 Replies Latest reply: Sep 24, 2009 6:08 PM by David Smalarz RSS

Call a SQL Stored Procedure

Laurie Crow
Currently Being Moderated

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
    Laurie Crow
    Currently Being Moderated

    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
    aRs .
    Currently Being Moderated

    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

     

     

  • Re: Call a SQL Stored Procedure
    Laurie Crow
    Currently Being Moderated

    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
    Jürgen Bauer
    Currently Being Moderated

    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
      Jürgen Bauer
      Currently Being Moderated

      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

       

Actions