Execute Stored Procedure

AutomatR.PostgreSQL.Database.ExecuteStoredProcedure

The "Execute Stored Procedure" activity in AutomatR for PostgreSQL databases allows you to execute a stored procedure and retrieve the result as a DataTable or output parameters, depending on your configuration. This activity is beneficial when interacting with PostgreSQL databases and executing custom procedures.

Properties

Name	Description
Input
Connection	Provide an NpgsqlConnection variable which will be used to establish a database connection. NpgsqlConnection variables containing the connection to be used.
Procedure Name	Provide the name of the stored procedure specified in the SQL parameter. String variables containing the name of the stored procedure.
Parameters	Specify the input and output parameters for the stored procedure. The parameters include the name, direction (In or Out), and datatype. Configure the values using the corresponding arguments.
Returns Table	If this option is selected, the stored procedure is expected to return a table of data. Boolean variables to indicate whether the stored procedure returns table data.
Misc
Display Name	Provides a customizable name for the activity displayed in the workflow. The display name enhances clarity and organization within the automation project. String variables containing the desired display name.
Optional
Delay	Specifies the amount of time (in seconds) to wait before executing the "Execute Stored Procedure" activity. Integer variables containing the delay duration. Example: If the amount of time is 1000 milliseconds or 1 sec, i.e., 1.
Command Timeout	Provide the amount of time (in milliseconds) to wait for the SQL command to run before throwing an error.
Output
Result	The result of the SQL command will be stored in a DataTable variable if the stored procedure returns table data. DataTable variables to store the result of the query execution.
Out Variables	If the stored procedure has output parameters, their values will be stored in a dictionary. Dictionary variables to store the values of output parameters.

How to use:

1. Drag and drop the "Execute Stored Procedure" activity onto the workflow.
2. Configure the properties by specifying the NpgsqlConnection variable obtained from the "Connect" activity, the stored procedure name, and input/output parameters.
3. Optionally, configure the delay, command timeout, and customize the display name.
4. Execute the workflow to execute the specified stored procedure on the PostgreSQL database and retrieve the result as a DataTable or output parameters.

Example: Consider an example where the "Execute Stored Procedure" activity is used to execute a stored procedure named "sp_get_employee" to retrieve employee data:

Execute Stored Procedure:
  Delay: 2
  Connection: myDBConnection
  Procedure Name: "sp_get_employee"
  Returns Table: True
  Parameters:
    - Name: "employee_id"
      Direction: In
      DataType: Int
      Value: 101
    - Name: "employee_name"
      Direction: Out
      DataType: String
      Value: ""
  Command Timeout: 5000
  Result: employeeData
  Out Variables: employeeName

In this example, the activity executes the "sp_get_employee" stored procedure with input parameter "employee_id" and output parameter "employee_name". The result, representing the employee data, is stored in the DataTable variable "employeeData," and the output parameter "employee_name" is stored in the String variable "employeeName" for further processing in the workflow.