Using the Run stored procedure action

Stored procedures are a set of SQL statements that are created and stored in the database. These SQL statements might be complex and have to run multiple times. Use the Run stored procedure action to execute existing stored procedures.

Note:
  • This action does not support Microsoft Access or SQLite database types.
  • Starting from Automation 360 v.22 (Build 10526), for stored procedures, the limit of 32 characters for input and output parameters has been removed.

Procedure

To automate the task of executing a stored procedure, follow these steps:

  1. Enter the name of the session you used to connect to the database server in the Connect action.
    You do not have to provide the details of the database server here because you have already associated those details with the session name when using the Connect action.
  2. Enter the name of the stored procedure.
  3. Optional: Click Add parameter to provide an input parameter or configure an output parameter.
    Select from the following parameter options:
    OptionSteps
    Input Parameter is passed from the bot to the stored procedure.
    • Name: Enter the name of the parameter from the stored procedure.
    • Value (optional): Enter a value or select a variable.
    • Type: Select the data type from the following options
      • BIGINT (number)
      • BINARY (Boolean)
      • BIT (number)
      • CHAR (string)
      • DATE (date time)

        Supported format yyyy-mm-dd

      • DECIMAL (number)
      • DOUBLE (number)

        Default choice for decimal values.

      • FLOAT (number)
      • INTEGER (number)

        Uses 4 bytes to store an integer with a value from -2,147,483,648 to 2,147,483,647

      • LONGVARBINARY (Boolean)
      • LONGVARCHAR (string)
      • NUMERIC (number)
      • REAL (number)
      • SMALLINT (number)

        Uses 2 bytes to store an integer with a value from -32,768 to 32,767

      • TIME (date time)

        Supported format hh:mm:ss

      • TIMESTAMP (date time)

        Supported format yyyy-mm-dd hh:mm:ss.f, where f is fractional seconds

      • TINYINT (number)

        Uses 1 byte to store an integer with a value from 0 to 255

      • VARBINARY (Boolean)
      • VARCHAR (string)
    For example, if running the example function from the Manage stored procedure action, configure the following two input parameters and values for the sum_of_two function to add:
    • Parameter 1: num1, 5, TINYINT
    • Parameter 2: num2, 10, TINYINT
    Output Parameter is passed from the stored procedure to the bot.
    • Name: Enter the name of the parameter from the stored procedure.
    • Type: Select the data type from the following options
      • BIGINT (number)
      • BINARY (Boolean)
      • BIT (number)
      • CHAR (string)
      • DATE (date time)

        Supported format yyyy-mm-dd

      • DECIMAL (number)
      • DOUBLE (number)

        Default choice for decimal values.

      • FLOAT (number)
      • INTEGER (number)

        Uses 4 bytes to store an integer with a value from -2,147,483,648 to 2,147,483,647

      • LONGVARBINARY (Boolean)
      • LONGVARCHAR (string)
      • NUMERIC (number)
      • REAL (number)
      • SMALLINT (number)

        Uses 2 bytes to store an integer with a value from -32,768 to 32,767

      • TIME (date time)

        Supported format hh:mm:ss

      • TIMESTAMP (date time)

        Supported format yyyy-mm-dd hh:mm:ss.f, where f is fractional seconds

      • TINYINT (number)

        Uses 1 byte to store an integer with a value from 0 to 255

      • VARBINARY (Boolean)
      • VARCHAR (string)

    When the bot runs, the action converts the database data type to an Automation 360-supported data type.

    For example, if running the example sum_of_two function from the Manage stored procedure action, configure the output parameter: sum, TINYINT.
    InputOutput Parameter can be used for both input and output.
    • Name: Enter the name of the parameter from the stored procedure.
    • Value (optional): Enter a value or select a variable.
    • Type: Select the data type from the following options
      • BIGINT (number)
      • BINARY (Boolean)
      • BIT (number)
      • CHAR (string)
      • DATE (date time)

        Supported format yyyy-mm-dd

      • DECIMAL (number)
      • DOUBLE (number)

        Default choice for decimal values.

      • FLOAT (number)
      • INTEGER (number)

        Uses 4 bytes to store an integer with a value from -2,147,483,648 to 2,147,483,647

      • LONGVARBINARY (Boolean)
      • LONGVARCHAR (string)
      • NUMERIC (number)
      • REAL (number)
      • SMALLINT (number)

        Uses 2 bytes to store an integer with a value from -32,768 to 32,767

      • TIME (date time)

        Supported format hh:mm:ss

      • TIMESTAMP (date time)

        Supported format yyyy-mm-dd hh:mm:ss.f, where f is fractional seconds

      • TINYINT (number)

        Uses 1 byte to store an integer with a value from 0 to 255

      • VARBINARY (Boolean)
      • VARCHAR (string)
  4. Enter the maximum number of records to retrieve.
    You can limit the results of the execution.
  5. Optional: Enter a timeout value.
    When the specified time expires, the statement execution stops even if the execution is not completed.
  6. Optional: Select the Export data to CSV option to save the retrieved data.
    Note: The Export data to CSV option does not support Oracle Database or PostgreSQL Server database types. Use output parameters to retrieve values.
    1. Select the file path from the Bots folder, the local device, or an existing file variable.
    2. Select the CSV file encoding to be either ANSI, UNICODE, or UTF8.
    3. Select whether to export the CSV file with or without the column headers.

      With column headers

      CustomerName City
      Manny Pittsburgh
      Kate Los Angeles
      John Boston

      Without column headers

      Manny Pittsburgh
      Kate Los Angeles
      John Boston
    4. Specify whether to overwrite the file or append the data to the existing file if a CSV file with the same name exists.
  7. Optional: Select a dictionary variable to hold the output if the stored procedure returns a value.
    Each output parameter name is a dictionary key which holds the corresponding parameter value.
    For example, if you assign the sum_of_two function output to $Output$, when the bot calls $Output{sum}$, the variable returns 15.
    Note: If you do not know the output parameter name, use the default key Output appended by the parameter index number.
  8. Click Save.