Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

How to call stored procedure (SQL Server) in alteryx

anbugans
8 - Asteroid

Hi ,

 

I have stored procedure which pulls the data from table (@table_name - parameter) and insert the records into another table. I want to call this procedure to read tables dynamically and execute this procedure to load the data into another table. Please help on this to achieve the same.

 

Thanks

Gans

12 REPLIES 12
SeanAdams
17 - Castor
17 - Castor

hey @Gina2021  - calling a stored procedure that brings back a select is just a matter of putting it in to the SQL editor like the screenshot below.

However - if what you're doing is a parameterised SP, alteryx doesn't have an easy way to pass in parameters as variables, you have to invoke the SP using parameters as literals 

e.g. "exec SelectStuff2 @param1 = 1,@Param2 = 1"

 

To do this - you need to dynamically change your query - either using a Dynamic Input tool or a macro.

Dynamic Input tool will also allow you to change Where clauses, so you may not need to use an SP.

 

 

Screenshot 2021-04-07 171318.png

 

 

wsalaz01
5 - Atom

Hi

Thank you so much for the info... very useful.  New to Alteryx.  I am getting an error "Store Procedure are not supported for this type of connection "

 

Any hints on what type of connection should I have/make?

 

Wil

 

Thanks in advance.

Aguisande
15 - Aurora
15 - Aurora

Hi @wsalaz01 

Most probably you have your connection as User, it should be System.

Remember, SP work only for Microsoft SQL Server, Oracle, or SAP Hana.

Labels