Invoking Storage Procedures and Functions

Component SQL in custom flow allows invoking not only SQL queries but also Storage Procedures and Functions.

The following examples demonstrate the basic examples for every supported database type

MySQL

DDL script of Storage procedure which returns value passed as the parameter

CREATE PROCEDURE `echo`(IN message VARCHAR(64))
  BEGIN
    SELECT message;
  END

The code of SQL query in SQL component for that procedure is

CALL echo('hello')

The response of SQL component is

[
    {
        "message": "hello"
    }
]

MSSQL

DDL script of Storage procedure which returns value passed as the parameter

CREATE PROCEDURE echo(
  @message VARCHAR(64)
)
AS
  BEGIN
    SELECT @message as message
  END
GO

The code of SQL query in SQL component for that procedure is (both variants are valid)

EXECUTE echo 'hello'

{CALL echo('hello')}

The response of SQL component is

[
    {
        "message": "hello"
    }
]

Oracle

DDL script of Function which returns value passed as the parameter

CREATE OR REPLACE FUNCTION echo(message VARCHAR2) RETURN VARCHAR2 IS
  lv_temp VARCHAR2(64);
  BEGIN
    SELECT message
    INTO lv_temp
    FROM dual;
    RETURN lv_temp;
  END;

The code of SQL query in SQL component for that function is

SELECT echo('hello') AS message FROM dual

The response of SQL component is

[
    {
        "MESSAGE": "hello"
    }
]

Postresql

DDL script of Function which returns value passed as the parameter

CREATE OR REPLACE FUNCTION echo(message VARCHAR(64)) RETURNS VARCHAR(64) AS $$
    BEGIN
      RETURN message;
    END;
$$ LANGUAGE plpgsql;

SELECT echo('hello') AS message

The response of SQL component is

[
    {
        "message": "hello"
    }
]