Tableau Desktop and Web Authoring Help

A stored procedure is a subroutine available to 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.

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

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

From the left pane, drag the procedure to the canvas or double-click one of the listed procedures. If parameters are available in the procedure, the Parameters dialog box automatically displays. 

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

If you then expose the Tableau parameter in the view, users are able to change the value of the parameter in the procedure interactively.

Stored procedures don't support federation, relating or joining. They are represented in a single logical table in the logical layer and don't allow opening the Join/Union canvas (physical layer).

Notes on Stored Procedures

If you use 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 one and ignores the rest.

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

• Stored procedures that have parameters of a non-scalar type are excluded.

• Result set columns that don't have matching types in Tableau (such as varbinary, geometry, and hierarchyid) are logged. If all result set columns map to unknown data types, Tableau displays a message:

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

• Stored procedures that return no result sets are listed on the data source page but fail if selected.

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

• Tableau does not perform any transaction management for stored procedures. That is, stored procedure writers must not depend on Tableau to start transactions before invoking stored procedures, or to commit them afterwards.

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

• If there are multiple queries in a stored procedure (for example, to read values from another table or to hold temporary combinations) each of the queries must return the same sets of columns in the same order (same names and data types). To ensure that column order and names match in the query results, you may need to explicitly CAST to ensure the data type is correct, for example CAST(Username as VARCHAR(20)), and explicitly name the columns. If a stored procedure does not follow these guidelines, an error message can result:

  "InsertData: unbound column error"

• If there are multiple queries in a stored procedure (for example, to read values from another table or to hold temporary combinations) and the procedure is generating an error, try adding SET NOCOUNT ON to the top of the procedure.This prevents the message which shows the count of number of rows affected by a Transact-SQL statement from being returned as part of the result set for a query.

In addition, the following constraints apply for specific databases.

Stored Procedure Constraints for Teradata Databases

The following constraints apply for stored procedures on Teradata databases.

• Values must be provided for every parameter. If the user does not provide a value for one or more parameters, Tableau displays a Teradata database error stating there are too few values provided for the stored procedure.

Stored Procedure Constraints for SQL Server Databases

The following constraints apply for stored procedures on SQL Server databases.

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

• If the total width of the result set (number of bytes in each row) exceeds 8060, the stored procedure fails. This can occur with very wide tables (hundreds of columns) or with tables having large text columns, intended to hold thousands of characters of text.

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

• If the user does not provide a value for one or more parameters that the procedure requires, Tableau displays a SQL Server database error in the form "The procedure requires a value for parameter @x but one was not provided."

• Stored procedures that contain multiple queries should follow the guidelines listed in Notes on Stored Procedures (above).

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

Stored Procedure Constraints for SAP Sybase ASE Databases

The following constraints apply for 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 the procedure requires, Tableau displays a Sybase ASE database error in the form "The procedure requires a value for parameter @x but one was not provided."