Welcome to the Alteryx Knowledge Base

Alteryx Designer

Stored Procedure FAQ

What are Stored Procedures?

Stored Procedures are a set of SQL statements (i.e., a subroutine) that are saved as a group and given a name so that the process can be easily reused and shared.

Why use Stored Procedures?

A benefit of the Stored Procedures is that you can centralize data access logic into a single pane that is then easy for the database administrators (DBA) to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a Stored Procedures but an individual user will not need to have read/write permissions on the underlying tables.

Which Databases are Stored Procedures Supported for in Alteryx?

1. Microsoft SQL Server
2. Oracle
3. SAP Hana

What Stored Procedures are displayed?

Only Stored Procedures with Parameters Input. We filter out all Stored Procedures with Output Parameters because it is not clear how to handle stored procedures with Output parameters in context of workflow execution.  

Which Tools can Stored Procedures be Accessed From?

Stored Procedures are only accessible using the standard Input tool and the Dynamic Input tool. More information can be found here Choose Table or Specify Query Window.

When using a Stored Procedure with the Dynamic Input Tool, the Value should be unique so that it is easily replaced in the Update Stored Procedure mode.

For Microsoft SQL Server: Stored Procedures can be accessed in the Stored Procedures tab and in the Pre SQL Statements or Post SQL Statements window.
For Oracle and SAP Hana: Stored Procedures can be accessed only in the Pre SQL Statements or Post SQL Statements window.

How are the Stored Procedures Executed when they are Called from Alteryx?

The Alteryx engine is not used when executing a stored procedure. The execution happens entirely on the database server.

How Do I Create Stored Procedures, and Execute them in Alteryx?

Below are a series of examples that walk you through from creating the Stored Procedures (SP) through the execution of them inside of Alteryx.

Example 1: SQL Server

In SQL Server Management Studio:

Use [Digan]  Create PROCEDURE Order_Priority AS Select * FROM [Demo].[dbo].[US_Transactions_Demo]

Here, I am creating a Stored Procedure (SP) in my [Digan] database that is selecting all the records from my US_Transactions_Demo table. This SP will bring back all the columns and records within the US Transitions Demo table.

Alteryx View:

After you connect to the database, click on the Stored Procedure tab and you should see a list of all the SP’s you should have access too. Below we see our Order_Priority SP that we just created.

SQL Editor Tab:

In the SQL Editor Tab, it should automatically populate based on the SP you have just select. In our case, it is:

EXEC Order_Priority

Running the Workflow:

The results should look like below. As described earlier, it should bring back the full table.

Example 2: Adding User Parameters

In this example, we are going to add user parameters to the SP. This should return the results based on the user's input.

In SQL Server Management Studio:

The Order_Priority_CriticalFilter SP below is adding a user input, @Order_ Priority, and grabbing all the records where the Order_ Priority column is equal to the user input.

Create PROCEDURE Order_Priority_CriticalFilter @Order_Priority nvarchar(30) AS Select * FROM [Demo].[dbo].[US_Transactions_Demo] WHERE [Order_Priority]=@Order_Priority

Alteryx View:

Here my user input for Order_Priority column filter is set to ‘Critical’. Make sure you respect the datatype of the SP. If it is a string, make sure to add the quotes around the parameter.

SQL Editor Tab:

EXEC Order_Priority_CriticalFilter 'Critical'

This is automatically populated from the Stored Procedure Tab.

Running the Workflow:

In the results, we see only the results where the Order_Priority is equal to Critical.

Example 3: Multiple User Inputs with Aggregations

Creating the SP in a SQL Server:

Create PROCEDURE Order_Priority_Most_Sold_Items @Product_SubCategory nvarchar(max), @Order_Priority nvarchar(max) AS Select [Product_Sub-Category], 
     [Order_Priority], 
     count([Product_Sub-Category]) as 'Category Count', 
     sum([Sales]) as 'Sum Sales'  
FROM [Demo].[dbo].[US_Transactions_Demo]   WHERE [Product_Sub-Category]=@Product_SubCategory AND [Order_Priority]=@Order_Priority GROUP BY [Product_Sub-Category], [Order_Priority] 

In this example, we are creating an SP that is going to have 2 user inputs, Product_SubCategory and Order_ Priority.

In the SP, we are doing the following:

1. Grabbing the Product_Sub-Category, Order_Priority, and the Sales column.
2. Using the count function to get the occurrence of the Product_Sub-Category table and renaming the column to 'Category Count',
3. Finding the total sales (sum clause) and renaming that column to 'Sum Sales'.
4. Filtering based on the user input by Product_Sub-Category and Order_Priority.
5. Lastly, Grouping by Product_Sub-Category and Order_Priority to get the aggregation of the table.

Alteryx View:

Here my user input for Product_Sub-Category is set to ‘Labels’ and Order_Priority is set to ‘Not Specified’.

SQL Editor Tab:

EXEC Order_Priority_Most_Sold_Items'Labels', 'Not Specified'

Running the Workflow:

Grabbing all Stored Procedures in the given database

The best way to grab all the SP’s in a given database is to use the information_schema. As long you are not in the master database, system stored procedures will not be returned.

select *    from DB_NAME_HERE.information_schema.routines   where routine_type = 'PROCEDURE'

If for some reason, you have a non-system SP in the master database, you can query:

select *    from master.information_schema.routines   where routine_type = 'PROCEDURE'     and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

Note, this will filter out MOST system Stored Procedures. More information for Stored Procedures can be found here.