How to call stored procedure (SQL Server) in alteryx

[anbugans]

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

[SeanAdams · Accepted solution]

:-) 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.

[timewaste]

@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_3) instead of exec sp_my_procedure(1,2,3)

Please advise.

Thanks!

[SeanAdams]

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]

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]

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

[NamrataDhiwar]

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]

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]

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]

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