I'm getting the same error as the original poster. It would be nice if Alteryx was able to execute SQL Scripts with temp tables. I don't see a need to have a stored procedure, nor do I have permissions to create stored procedures in my environment, so the suggestion won't work for me. In the example below, I am trying to output the last sql statement (the select of the top 50 rows). Here's my SQL:
if OBJECT_ID('tempdb..#mytemp') is not null drop table #mytemp;
...in the Post SQL Statement dialog...although technically the system should take care of this.
I'm wondering if the issue is that there is no #mytemp table to include in the Table Select? What I ended up doing was manually typing in the Select statement in the SQL Query tab of the editor. (BTW...if you test the query from there it will fail, but runs just fine during run time.)