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]

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.

[wsalaz01]

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]

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.

[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