Stored procedures are available to complement the data available from the Data Model.

Executing Stored Procedures

Stored procedures can be executed using the custom SQL window that appears when selecting New Custom SQL from the bottom of the table list.

Procedures can be executed using the EXECSP system table with the following syntax.

SELECT * FROM EXECSP ('SchemaName.ProcedureName',StoredProcedureInput1,StoredProcedureInput2,...)

SchemaName denotes the name of the connection that the table list is pulled from. ProcedureName is the name of the stored procedure to execute.

Note that the stored procedure inputs are dynamic, there will be as many as are documented (and in the same order documented) in the procedure's page in the stored procedures section.

EXECSP vs EXEC

The EXEC syntax may be familiar to users of others editions and is often used in the documentation for stored procedures:

EXEC ProcedureName Name1=StoredProcedureInput1, Name2=StoredProcedureInput2, ...

The EXECSP syntax is different from the EXEC syntax in two important ways:

• Parameters may not be named in the EXECSP syntax. Invoking EXECSP as EXECSP('SchemaName.ProcedureName', Name1=StoredProcedureInput1, ...) does not return the expected results because Name1=StoredProcedureInput1 is treated as a comparison.
• EXECSP parameters must be provided in the order they are listed in the documentation. To omit a certain parameter, it should be given as NULL.

Stored Procedures with Dynamic Output

Tableau executes custom SQL in two stages: the first stage determines what columns the query outputs, while the second stage executes the query against Sage 300. In most cases this is not a problem because stored procedures have a pre-defined set of outputs.

However, some stored procedures have dynamic outputs that depend on the data in Sage 300 or the input values given to the procedure. The connector can only determine the output columns by executing these kinds of procedures. This means that they must be executed twice: once to discover their schema and once to get their output data.

By default the connector prevents this, because some procedures may modify the data in Sage 300 and have unintended effects if run more than once. You can disable this error by adding "EnableExecSpSchemaOnlyQuery=true" to the connector's Connection String.