Call Stored Procedure Version 2 (Java)

Action group: Databases

Description

The action executes a call to a stored procedure with parameters. Compatible with MS SQL Server

Action icon

Settings of parameters

PropertyDescriptionTypeFilling exampleMandatory field
Parameters
DB contextConnection context to the selected databaseRobin.DbContextDatabase contextYes
Stored procedure nameStored procedure nameRobin.String
Yes
Parameters listA collection of parameter values to be substituted into a stored procedure callRobin.Collection
No
Results
Results of a stored procedure executionCollection of stored procedure results. Each result is a data table If result is a row set then data table contains it. If result is an update count then data table contains one column "update_count" and one row with the value. Hint: if you don’t need update counts just use in stored procedure operator suppressing return of counts. For MS SQL Server - SET NOCOUNT ONRobin.Collection

Special conditions of use

A stored procedure is prepared SQL code that will be saved so that the code can be reused over and over again.

So if you have a SQL query that you write over and over again, save it as a stored procedure and then just call it to execute it.

You can also pass parameters to the stored procedure so that the stored procedure can act based on the parameter values passed in.

Creating a stored procedure

Executing a stored procedure 

More about stored procedures here.

Example of use

Task

Connect to the database and get the entire Organizations table  

using the previously created stored procedure

Solution

Use the actions "Connect Standard Db", "Call Stored Procedure", "Get value by index", "Table to CSV". 

Implementation

  1. Assemble a robot consisting of actions: 
  2. Configure the settings for the "Connect Standard Db" action.

    In the "DBMS type" field, select the appropriate database to which you want to connect. 
    Specify the database login and password. 
    The server host can be specified either by IP of the host where the database is installed or by host name. 
    Specify the server port, as well as the database name. 
    In the "Timeout" field, the value is calculated in seconds. 
    In the "Parameters" field specify additional parameters for connection, usually these are parameters that relate to security. Parameters are specified in the form of a dictionary. 

    Learn more about the "Transaction isolation level" parameter. Sequential database operations can be divided into transactions. A transaction is a group of operations that represent a certain boolean unit of work with the database. For example, it is necessary to perform several operations with a table in order to update the data in it. If an error occurs, you can roll back the transaction, i.e. a group of operations made, so that you don't have to figure out at which step this error occurred. 

    If the "Transaction isolation level" checkbox is activated, a part of the executed queries to the database will be in the form of transactions. 





  3. Set the parameters for the "Call Stored Procedure" action. 



  4. Click on the "Start" button in the top panel. 

Result

The program robot completed successfully. The collection that contains the resulting table was returned.