Support for Stored Database Procedures

Document created by Chris Sommerstad Employee on Jul 20, 2017
Version 1Show Document
  • View in full screen mode

Support for stored procedures/functions is provided for both PostgreSQL and SQL Server.

A database action (in a clip element) may contain one or multiple SQL statements and/or stored procedure and function calls. Since only one output option can be specified per database action, the same output option will apply for all the SQL statements and or stored procedures and functions for the same database action.

Note that the output options for CSV, JSON, and XML formats apply to queries only. When one of those output options is specified for a database action that contains both queries and non-queries, the output option of Summary will be used for the non-query statement(s).

For example, for a database action that contains two SQL statements: INSERT and SELECT. If the output option is set to "xml-with-summary" (which is for queries), the output format for the INSERT statement will be "summary" (and the output format "xml-with-summary" will be used for the SELECT statement).

Note also that the output options for CSV formats cannot be used for multiple SQL statements and/or stored procedure/function calls because the columns in the results can be different. A stored procedure can contain one or multiple SQL statements. If it contains one SQL statement, the result format is same as current. In the following screenshot, a stored procedure for PostgreSQL is shown.

In the following screenshot, the database action shown consists of three stored procedure calls and two queries. There are five results respectively.

Stored Procedures in the Result Details Dashboard

In the following screenshots, five results are shown individually.

The first of the five (Result 1/5) results is shown below.

Note that the following three screenshots correspond to the first three results for the stored procedure calls (shown above). Three columns of data are shown for the given row.

Each of the remaining three results contains three columns of data.

The fourth query returns one column of results.

The fifth query returns two columns of results.