Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop 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

14 REPLIES 14
SeanAdams
17 - Castor
17 - Castor

:-) great question @anbugans

 

We do this frequently.

 

The easy way is to drop an input tool onto your canvas - you can either use an input tool that does something useful (if you already are inputting data) or you can drop on a fake one that just does something arbitrary (e.g. "Select 1") as the query.

The trick though is to use the Pre-SQL section, and pop in the text "exec sp_my_procedure" where sp_my_procedure is your sproc that you're trying to call.

 

2017-05-09_8-12-21.png

timewaste
8 - Asteroid

@SeanAdams Thanks for posting this...

Can you please answer two questions?

 

1. Will this work for Oracle too?

2. Secondly, is there a way we can pass variables in arguments in Pre-SQL statement? And if so, how do I define variables in Alteryx?

 

For example using above:

exec sp_my_procedure(var_arg_1,var_arg_2,var_arg_3instead of exec sp_my_procedure(1,2,3)

 

Please advise.

 

Thanks!

SeanAdams
17 - Castor
17 - Castor

Hi @timewaste

Not sure if this works for Oracle - anyone else on the community use Oracle that can check?

 

In terms of parameters - as far as I'm aware the only way to do this would be using an action tool to modify the XML.   Well worth you posting this as an idea in the product ideas section for the designer here;

 

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas

 

SeanAdams
17 - Castor
17 - Castor

Hi @timewaste

Not sure if this works for Oracle - anyone else on the community use Oracle that can check?

 

In terms of parameters - as far as I'm aware the only way to do this would be using an action tool to modify the XML.   Well worth you posting this as an idea in the product ideas section for the designer here;

 

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas 

 

BARTONCONNIE
6 - Meteoroid

To handle parameters, use the SQL DYNAMIC INPUT tool.  It allows you to select a stored procedure and to set the parameter values.

NamrataDhiwar
8 - Asteroid

Thanks for answering question.

 

I have tried with Pre SQL statement. But  i want to track Stored Procedure Ran with error or success . How can i achieve this?

SeanAdams
17 - Castor
17 - Castor

Hey @NamrataDhiwar 

Alteryx doesn't have a way of managing rows that have an error like some of the other ETL platforms do.   What Alteryx will do is report an error if the SP throws an error.

 

If you need to continue processing - then a better way is to design the SP to return data, and call this using a dynamic input.

You could return a record set with a 1 if it was successful, and a zero if it fails - that way you can continue processing rather than your canvas failing because of a SQL error

Gina2021
8 - Asteroid

Hi @BARTONCONNIE ,

 

Do you have an example workflow of this by chance? I'm trying to find a way to store a dynamic variable that will change the WHERE clause of the dynamic input SQL query. 

 

Thanks tons!

Gina

Gina2021
8 - Asteroid

Hi @SeanAdams ,

 

Thanks for this! Do you by chance have a workflow example?  I'm diving into stored procedures for the first time today and am shaky on if it will accomplish what I'm looking for.

 

I'm trying to find a way to store a dynamic variable that will change the WHERE clause of the dynamic input SQL query. 

 

Thanks tons!

Gina

Labels