We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Stored Procedures FAQ

DiganP
Alteryx Alumni (Retired)
Created

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

Which Tools can Stored Procedures be Accessed From?

Stored Procedures are only accessible using the standard Input tooland 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.

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.

Ex1.png

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.

ex12.png

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.

Ex2.png

SQL Editor Tab:

EXEC Order_Priority_CriticalFilter 'Critical'

This is automatically populated from the Stored Procedure Tab.

Running the Workflow:

Ex21.png

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’.

Ex3.png

SQL Editor Tab:

EXEC Order_Priority_Most_Sold_Items'Labels', 'Not Specified'

Running the Workflow:

Ex31.png

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.

No ratings
Comments
amfitz
5 - Atom

Hi @DiganP,

 

I'm using the Input Data tool to try to run my SP. My SP is currently sitting in SQL and can be executed without a problem via SQL itself and even using the run command line on my computer. The issue comes when I try to use Alteryx. In your example above, it seems that my SP should pop up after I connect to my database (pop up on the Stored Procedures tab). That tab is blank for me and I'm unable to add anything.

 

Thoughts?

r9dayts
5 - Atom

I am also unable to see stored procedures

godoyau
6 - Meteoroid

I tried a slightly different version of the procidure shown in the example. In the procidure used below, the result is temporary, but Alteryx is unable to read the return giving the following error message:

 

image.png

 

Does anyone know if we actually have this limitation when we use alteryx to consume a procidure that returns a select from a temporary table?

 

Proc exemple:

 

CREATE PROCEDURE [dbo].[TESTE_MODELO]

@SEGMENTO INT

AS
BEGIN

SELECT TOP 10 * INTO #TMP FROM DBSH295..TB_SOLICITACAO WHERE ID_SEGMENTO = @SEGMENTO

SELECT TOP 5 * FROM #TMP

END

 

I have tried with a more complex proc and have the same problem:

 

https://community.alteryx.com/t5/Alteryx-Designer/INPUT-DATA-CONSUMING-SQL-PROCIDURES/m-p/618787#M13...

sjain71
7 - Meteor

Hi,

 

Thanks for the information. Can we use the system variable such as 'workflow name ' as a value in stored procedure execution? Help appreciated!!!

 

Thanks,

Saurabh