How can I interact with a Function Statement in SQL Server in ArcGIS?

[JacquelinePursell]

I previously have only used Views to do complex queries but recently one of our database guys made me a couple of functions instead because he is pulling data from multiple databases and doing some complex things.  These are not showing up in the table list like a view does so I can't plot the XY manually in ArcMap.  To remedy this, he made a View out of the Function but in SQL Server Management Studio it takes 12 seconds to select the top 1000 records and in ArcGIS it never loads.

Is there a way to interact with SQL Function Statements in ArcGIS?

[JacquelinePursell]

currently part of the data is dynamic and part of it is only nightly because they are migrating from an Access database and only parts have fully moved over.  So assuming that soon it will be pretty dynamic, having it "live" is preferable.  I have published several temporary event layers in map services from views built in Oracle and SQL Server databases and it has worked out well, a second or 2 delay, but it is well worth it and my users are ecstatic.  

Also, I do truncate and append my SDE with this data and all my other data sources nightly using FME so that my GIS users can have data to work with in their maps without needing to connect to 10 databases and sifting through tables like I have to. 

[JacquelinePursell]

That is a great idea, however, I will keep it open as an option if I have to.

[AlbertoAloe]

Using Query Layer in ArcGIS/ArcGIS Pro should solve the problem.

I imagine you have table valued functions with parameters.....just enter the sql as you would write it in SSMS and it should work even if your function does not appear in the object list.

For example, in my case i have a table valued function called fnCatchDownStream that takes an input parameter called HydroID.

I use it this way in SSMS as well as in Query layer....

SELECT * FROM fnCatchDownStream(356987)

Alberto

[JacquelinePursell]

I actually did try that but I got an error saying that it couldn't find the table.  I can do this in FME and SSMS but not ArcGIS.

[GayleNeufeld]

Did you ever figure out if you could use the Function (a sql script that pulls data from many different tables/databases and also performs calculations) in ArcGIS Online?  I find myself in this same situation.  Our data is way too complicated to pull together in anything other than a function.