How to call stored procedure (SQL Server) in alteryx

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

Dynamic Processing

Accepted answers

SeanAdams

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

All comments

SeanAdams

:-) great question @anbugans

We do this frequently.

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

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

amir0606

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

SeanAdams

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

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235