Store Procedure in Snowflake

[rohit782192]

Hi,

I want to know how we call a Store Procedure in Snowflake and update also.

[amirsemsar]

There is no fix for this what i would recommend is to utilize a python tool and use the the python connector to do it 


please see the othe post: 
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Access-to-Stored-Procedures-in-Snowf...

[mblessing]

This is a bit old, but I encountered this problem of calling a stored procedure in Snowflake within Alteryx today and was able to call a stored procedure in Snowflake via Alteryx through a bit of dumb luck, and fairly easily.

I just dropped an input tool that was connected to Snowflake, I defined the warehouse, database and schema in the Pre-SQL statement box in the configuration: (use warehouse XYZ; use database XYZ; use schema XYZ;)

And then in the actual query, I called my stored procedure (call stored_procedure), but the key that made this work is to put a commented out piece of SQL code, with a 'select' and 'from' statement, prior to the calling of the stored procedure.

For instance, I have: 

--select a from b
call stored_procedure

as my query and this runs the stored procedure with no issue. I explored this a bit, and it's like Alteryx won't run the stored procedure simply by calling it in the query window, but if you comment out a line of SQL code, it tricks it into running it anyway. I discovered that the commented out line MUST contain a "select" and "from", otherwise it will error out running the stored procedure, hence the "select a from b" in my code above. 

Hopefully this works for you, as well!

[apathetichell]

Cool! I wonder if this is ODBC driver behavior vs Alteryx specific behavior. I'd be curious if you connected via Powershell if you observed the same behavior.

[mblessing]

Good question, I assume this is a quirk of the ODBC driver, but can't completely rule out that it may be an Alteryx specific issue. I may give that Powershell idea a go and see how it behaves.

[amirsemsar]

The best fix I found so far is to create a python macro that utilizes SQL alchemy to do the call you can also make this a batch macro where you can feed multiple queries into the macro and have it run multiple processes. This goes beyond the scope of alteryx's ability to execute queries now you can do way more.

 

[apathetichell]

@amirsemsar not if you are using SSO. And I use batch queries all the time. The @mblessing solution is 🔥

[amirsemsar]

I only posted what I did. I don't see a counter solution presented just hypotheticals. The python macro is in production and doing well.

[Samantha_Jayne]

hi, @mblessing curious about how your store procedures are setup in Snowflake are you utilising Python...

I have also done this trick for SQL Server and hit a roadblock applying to Snowflake. So wondering about the setup on the Snowflake side to understand if its that, at the moment I am getting no columns returned error.

[mblessing]

Hi @Samantha_Jayne 

Sorry I didn't see your reply so long ago! It's been a bit, so I don't recall the exact specifics, but I do believe my stored procedure involved Python at the time of this post.