topic Re: How to call stored procedure (SQL Server) in alteryx in Alteryx Designer Desktop Discussions

How to call stored procedure (SQL Server) in alteryx

anbugans — Thu, 24 May 2018 03:43:45 GMT

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

Re: How to call stored procedure (SQL Server) in alteryx

SeanAdams — Tue, 09 May 2017 02:43:56 GMT

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

Re: How to call stored procedure (SQL Server) in alteryx

timewaste — Tue, 29 May 2018 20:02:09 GMT

@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!

Re: How to call stored procedure (SQL Server) in alteryx

SeanAdams — Wed, 30 May 2018 10:34:42 GMT

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

Re: How to call stored procedure (SQL Server) in alteryx

SeanAdams — Wed, 30 May 2018 10:37:59 GMT

Hi @timewaste

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

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

Re: How to call stored procedure (SQL Server) in alteryx

BARTONCONNIE — Thu, 01 Nov 2018 17:37:02 GMT

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

Re: How to call stored procedure (SQL Server) in alteryx

NamrataDhiwar — Thu, 31 Oct 2019 15:33:33 GMT

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?

Re: How to call stored procedure (SQL Server) in alteryx

SeanAdams — Thu, 31 Oct 2019 20:04:05 GMT

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

Re: How to call stored procedure (SQL Server) in alteryx

Gina2021 — Wed, 07 Apr 2021 14:13:22 GMT

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

Re: How to call stored procedure (SQL Server) in alteryx

Gina2021 — Wed, 07 Apr 2021 14:15:32 GMT

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

Re: How to call stored procedure (SQL Server) in alteryx

SeanAdams — Wed, 07 Apr 2021 21:24:03 GMT

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.

Re: How to call stored procedure (SQL Server) in alteryx

wsalaz01 — Thu, 15 Jul 2021 16:07:49 GMT

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.

Re: How to call stored procedure (SQL Server) in alteryx

Aguisande — Mon, 25 Oct 2021 15:43:33 GMT

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.

Re: How to call stored procedure (SQL Server) in alteryx

amir0606 — Sun, 17 Mar 2024 11:46:57 GMT

Hi, I am trying to run a SQL script on the SQL Server through Alteryx . I use input data tool to connect to SQL server database and add query to the tool, For a normal select or join etc. it works fine. However, it seems not working if my script includes assigning parameter or inset into a #temp table(sample code below), it won't pass the 'Test Query' even. Does Alteryx has the feature of 'Pass-Through', means pass whatever you input to the server side to run without checking anything? I have put the sample script below, it works when it runs on SQL Server directly.

DECLARE @M_Date date;

SELECT @M_DATE = MAX(CLOSING_DT) FROM TABLE1; <----- NOT WORKING

SELECT COL1, COL2

INTO #TEMP <-------------NOT WORKING

FROM TABLE2

WHERE SALES_DT = @M_DATE

Re: How to call stored procedure (SQL Server) in alteryx

SeanAdams — Tue, 26 Mar 2024 10:57:41 GMT

Hi @amir0606 - given this is an old thread for a slightly different topic, people may not find your question because many folk are only scanning newer threads. You may get better pickup by creating a new thread for this specific question.

My understanding - and the connectors team can confirm (copied below) - is that there is no "passthrough" mode on the SQL section of an input tool - but there are 2 potential ways that you can do what you're trying to do:

- Firstly - put this into a pre-SQL, and in that Pre-SQL you create a table that you can query in your main SQL. Not particularly pretty though because you leave tables lying around.

- Second - wrap this in a stored procedure on SQL - and then just execute that. Warning - if you have multiple recordsets returning, or insert and updates - you may have to turn off some of the flags that show recordset count otherwise this may also impact Alteryx.

BTW - one of the most requested features for Alteryx for a while has been the plain old SQL tool - to do inserts, updates, deletes and more complex SQL like you're doing here. Not sure where this is on the roadmap. It would be well worth you dropping an idea in the Ideas section https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/idb-p/product-ideas with your idea of what you're trying to achieve and the capability you are looking for.

CC: @wesley-siu @VojtechT