Table Valued Function - Returns

[garrjo]

@MikeBeaton - I'm attempting to Execute a function on SQL Server and as of Yet, haven't found a way to return the resulting table on the function. Should I be using the ExecuteProcedure or Query?

[mikebeaton]

Try QueryFromProcedure

[garrjo]

var fncSweeps = new Mighty.Sweeps().QueryFromProcedure("usp_SweepNavigation", inParams: new { Site = s }); --The request for procedure 'usp_SweepNavigation' failed because 'usp_SweepNavigation' is a table valued function object.

Or Should I run as a query with a arg?

[mikebeaton]

Okay I see, then you should be fine with:

var results = db.Query("SELECT * FROM usp_SweepNavigation(@Site)", inParams: new { Site = s });

[garrjo]

That works pretty well using the Query method; though, it would be nice to have to be able to run function table value based queries using the QueryFromProcedure || QueryFromFunction and get a yielded ienumerable result set.

[mikebeaton]

You do still get a yielded IEnumerable result when using .Query.

There is no way in the underlying SQL Server ODBC driver to 'directly' read from a table valued function. Mighty is an ODBC data access wrapper, and it (mostly...) doesn't add functionality which isn't in the underlying drivers. It just hopefully makes it much easier to use all the underlying functionality.

The SQL Server driver supports directly reading result sets from a stored procedure, or from any other arbitrary SQL only. And a function is not a stored procedure, nor is it a piece of SQL on its own. So you do have to either call it from within a stored procedure or call it from within an arbitrary piece of SQL to get at its results.

[mikebeaton]

Hi @garrjo,

If I've understood you correctly, you mentioned about this issue in in #7 "having to rely on String.Format options for argument syntax is cludgy", but I'm not quite sure what you mean.

Could you post here a quick code example showing what you have to do (relying on String.Format options) and another pseudo-code example showing what you'd like to be able to do instead?

[garrjo]

The issue was ref_cursor -> and the implementation required me to use a Parsed Query that contained the Parameter already...it was 'ugly'. But, that seems to be based on the ODP.net implementation and it's lack of support surrounding ref_cursor returns. Apparently the Java implementation supports the ref_cursor really well; whereas the odp.net implementation does not unless you are using the newer core version which does....

All a mute point, as I've switched out from this model...to more standard query process....due to limitations on ODP.net.

Thanks though.