cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query update statement stored procedure

0 Kudos

Hi Gurus,

I have a requirement wherein after reading and extracting the table from the SQL server, I need to call a stored procedure. I have read in the forums and blogs that EXECUTE stroredprocedure is not possible in Update SQL statement. It can only be used in Query SQL. If that's the case, how can I call the SP after the table has been extracted? The Stored Procedure has a parameter (a table field).

Much Thanks!

Kind Regards,

SAPEnthusiast

Accepted Solutions (1)

Accepted Solutions (1)

rajasekhar_reddy14
Active Contributor
0 Kudos

perform select and update operation(if update not required then do dummy update) in mapping level call stored procedure .

or

sender side evrything club in to one stored procedure(avoid select statement) and call stored procedure.

Answers (2)

Answers (2)

0 Kudos

i had no choice but to create multiple interfaces/ scenarios. bpm was not an option

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>If that's the case, how can I call the SP after the table has been extracted? The Stored Procedure has a parameter (a table field).

Combine all sql operations in the stored procedure and reference in the select statement. Provide some fake update statement in the sender channel. Handle all business requirements in the stored procedure.

0 Kudos

I think this is not at all possible. Business needs the stored procedure for updating the table in SQL only if the target message (IDOC or file) has been successfully created by PI. PI needs to check first if the target message has been created before calling a stored procedure. If the target message is not created, the stored procedure will still be called but with a different parameter.

But still, thanks for your inputs.

rajasekhar_reddy14
Active Contributor
0 Kudos

Now you explaine dyour requirement, here is the design for it.

Definlty you required BPM with combination of RFC/File lookup.

RFC loop give sthe status abput IDoc successfully posted or not.

File look up gives the status about File succesfull created or not.

Develop 2 mappings(one is for IDoc and other one is for File) and execute them in BPM after that call stored procedure(this mapping you need perfrom looks up).

Regards,

Raj