How to invoke a stored procedure

Arun
Arun Member
edited December 2023 in General

I wanted to execute a stored procedure from the workflow as its first activity. My credential has the required permission to execute the stored procedure which is created on SQL Server DB. The stored procedure takes care of creating a new entry to the log table with the information such as start time, 'Started' as the status; and it returns the Log Id of the newly inserted record, so this Log Id can be used in subsequent activities. Also, I wanted to execute another procedure as the last activity in my workflow, which takes care of updating the status as 'Completed' and current time as End Time for the Log Id which we generated at the beginning.

Answers

  • Henry Simms
    Henry Simms Administrator

    Hi @Arun

    When exporting to a JDBC source, you can optionally add SQL queries to be run on the database immediately before or immediately after the INSERT/UPDATE/DELETE , and using the same credentials. You do this using the Pre SQL and Post SQL, described in Export step documentation.

    The example below shows a workflow's Export step configured to execute a stored procedure before the export (Insert or Update) happens, and another when it completes.

    image.png

    If you wanted to call the stored proc without actually exporting any rows, I think you'd use the Export step in the same way, but on a zero-row input.

  • Arun
    Arun Member

    Thanks @Henry Simms , As you mentioned, I'm able to invoke the store proc from both the Source and Export steps and is working as expected. Thanks for your solution.

Categories