User Defined Functions/ Table Valued Functions

[sydsutton]

Hello,

I was wondering if you would be open to the idea of either adding and/or receiving a PR for User Defined Functions and Table Valued Functions? I use MS SQL Server, and would very much like to use SQL Hydra in conjunction with UDF's/TVF's. I might know someone who can help with the PR if that's the route you decide to go.

Thanks so much.

[JordanMarr]

I assume this feature would exist within the SqlHydra.Query library. Would it involve type generation? Can you give a code example of how you imagine it would be used?

[sydsutton]

Yes, it would involve type generation. And I will get back to you as soon as possible with a code example.

[sydsutton]

This is my current code, just with the table that I'm referencing taking a parameter instead of taking no parameter. In MS SQL Server, the table being called would take the "calculation" that was passed in and do some math based on the calculation.

[JordanMarr]

What kind of support does Microsoft.Data.SqlClient provide for getting metadata on table valued functions?

[JordanMarr]

While GetSchema / Procedures does list UDFs, including a custom table function that I made, GetSchema / Columns doesn't list any of the returned columns.

So there would need to be a metadata query that returns:

• The table valued function inputs (names, types)
• The table valued function output columns (names, types)

[sydsutton]

-- To get Table Functions
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE = 'TABLE'

-- To get Scalar functions, same as above except filter out DATA_TYPE 'TABLE'
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE <> 'TABLE'

-- To get output columns
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS -- Join on `TABLE_SCHEMA` and `TABLE_NAME`

-- To get input parameters
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS -- Join on `SPECIFIC_SCHEMA` and `SPECIFIC_NAME`

[JordanMarr]

This seems doable. However, there is some refactoring in process that needs to be completed before this can be started.

[sydsutton]

Ok, that sounds good. Thank you for being open to the idea. Please let me know if there's anything else you need from me.

[RJSonnenberg]

@JordanMarr Is there any movement on this? Our team has run into an issue where we have to run both table and scalar functions and get the results. Would this solution be able to support scalar functions in addition to the table functions?

[JordanMarr]

It would be a nice feature to have for sure, but it’s not something I need and therefore no movement. Currently, vanilla ado.net would probably be the easiest way to do it (unless you have a ton of functions, in which case I could understand why you would want this as a feature).

[sydsutton]

@RJSonnenberg I think I ended up using Dapper for this particular issue. I can send you more specifics in the morning if you need them.

[JordanMarr]

This feature looks like it could be challenging to implement because:

• different providers will likely have different queries to pull function names, inputs and outputs
• there are different kinds of functions, and some of them return custom types which is another feature that isn’t supported
• obviously the code generation piece would be difficult
• The Query library would need to somehow know that the generated functions were usable in the queries and that they should be spliced in (presumably by inspecting their path name)