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"
}
]
Updated almost 6 years ago