SQL statement (-S or -STMT)

Use the SQL Statement adapter command (-STMT) either for a source or in a GET function to specify an SQL statement or a call to a stored procedure using either the database-independent or native call syntax to be executed. This execution will provide the input data to a map.


-STMT SQL_statement

The "database-independent" syntax for invoking a stored procedure requires the query statement to commence with a "call" command. Following the "call" command, you must specify the input, output and return parameters. Use parentheses to enclose the input and output parameters, separating consecutive parameters with a comma. To indicate a return or an output parameter, use a "?" mark. If you do not need the return value, simply omit it, and if you want to disregard an output parameter, replace it with an 'X'.

Input parameters can be specified as they are or enclosed within single quotation marks, especially when the input contains spaces. If the input data itself includes a single quote, escape it by doubling it. For example:

-STMT “call sp_input_param (‘I’’m escaping a single quote in this input value.’)”

In cases where both input and output parameters are necessary, place the output parameter's '?' mark first, and delimit it from the input parameter using a forward slash character, '/'.

For example, consider the following statement that invokes a stored procedure with an input and output parameter. The first parameter is for input only, while the second one is for both input and output:

-STMT "call sp_params(param1,?/param2)"

If the above stored procedure includes a return value, it will be ignored unless you precede the stored procedure name with "?=". For example:

"call ?=sp_params(...)"