How do stored procedures work

Use a saved procedure

A stored procedure is a subroutine that is available for applications that access a relational database system. When you connect to a SAP Sybase ASE, Microsoft SQL Server, or Teradata database with Tableau, you can use a stored procedure to define the connection.

With Oracle, you can define the connection using a table function. Oracle table functions act similarly to the stored procedures and are listed under Stored Procedures on the Data Source page.

If you create a data source using one of these data types, the available procedures are listed under Stored Procedures, as shown in the example with Microsoft SQL Server.

Drag the procedure from the left pane to the work area or double-click one of the listed procedures. If parameters are available in the procedure, the Parameters dialog box appears automatically.

Instead of entering a value, you can use an existing Tableau parameter or enter a new Tableau parameter for the value:

Then, when you display the Tableau parameter in the view, users can interactively change the value of the parameter in the procedure.

Stored procedures do not support joins, relationships, or joins. They are presented in a single logical table on the logical level and do not allow the "Link / Union" linkage area to be opened (physical level).

Notes on stored procedures

When using stored procedures to define a data source for Tableau, keep the following in mind:

  • If a stored procedure returns more than one result set, Tableau reads the first and ignores the rest.

  • If a stored procedure has output parameters, Tableau filters out the stored procedure.

  • Stored procedures that have non-scalar parameters are not processed.

  • Result set columns with no counterparts in Tableau (such as "varbinary", "geometry", and "hierarchyid") are logged. If all of the result set columns are assigned to unknown data types, Tableau displays a message:

    "The result set ... has no usable columns."

  • Stored procedures for which no result sets are returned are listed on the data source page, but will result in an error if selected.

  • If no value is specified for a parameter that is actually necessary for the stored procedure, an error occurs. Tableau cannot determine in advance whether certain parameters are required.

  • Tableau does not perform transaction management on stored procedures. This means that procedure creators cannot rely on Tableau to start transactions before any stored procedures are called or that they are committed afterwards.

  • Column names must be unique for procedures to work. If two columns have the same name or if no name is specified, the procedure may result in an error.

  • When a stored procedure contains multiple queries (for example, to read values ​​from other tables or to accommodate temporary combinations), each query must return the same sets of columns in the same order (same names and data types). To ensure that the order and names of the columns in the query results match, you may need to use explicitly to ensure the correctness of the data type, for example, and explicitly name the columns. If a stored procedure does not follow these guidelines, it can result in an error message:

    "InsertData: Error due to unbound column"

  • If a stored procedure contains multiple queries (for example, to read values ​​from other tables or to include temporary combinations) and the procedure generates an error, try inserting at the beginning of the procedure. This prevents the message indicating the number of rows affected by a Transact-SQL statement from not being returned as part of the query's result set.

In addition, certain databases have the following restrictions.

Stored Procedure Limitations on Teradata Databases

The following restrictions apply to stored procedures with Teradata databases.

  • There must be values ​​for each parameter. If the user does not provide a value for one or more parameters, Tableau displays a Teradata database error and indicates that there are not enough values ​​for the saved procedure.

Stored Procedure Limitations on SQL Server Databases

The following restrictions apply to stored procedures on SQL Server databases.

  • If the result set for a saved procedure contains columns of the type "IMAGE" or "TEXT", the saved procedure generates an error with the error message "Incorrect syntax".

  • If the total width of the result set (number of bytes in each line) is greater than 8060, the stored procedure will result in an error. This happens with very wide tables (hundreds of columns) or with tables with large columns of text that are intended to contain texts with thousands of characters.

  • Tableau does not display stored procedures from schema "sys".

  • If the user does not provide a value for one or more parameters that is actually necessary for the procedure, Tableau will display a SQL Server database error of the following type: "The procedure requires a value for the @x parameter, but it is not available."

  • Stored procedures that contain multiple queries must follow the guidelines listed in "Stored Procedure Considerations" (above).

  • Tableau Desktop does not support the Microsoft SQL Server TIME data type. If there are fields of this type in a stored procedure in a Microsoft SQL Server database, Tableau Desktop will not import them.

Stored Procedure Limitations on SAP Sybase ASE Databases

The following restrictions apply to stored procedures on SAP Sybase ASE databases (Windows only).

  • The database must have a properly configured remote server.

  • If the user does not provide a value for one or more parameters that is actually necessary for the procedure, Tableau will display a Sybase ASE database error of the following type: "The procedure requires a value for parameter @x, but this value is not available."