@PaulWhite Like what? Multi-statement TVF or scalar function?
The other options already rejected were to suppress all rows due to invalid parameters or to return rows but all with NULL value and error units. The particular use case is providing users capability to convert between different units of measurement from the base unit of measurement for a particular type of measurement in the data mart because they have a preference.
In our internal conversions, we already validate all the source and destination units, so the version of that function doesn't really need any error checking like that.
@CadeRoux "Like what" depends entirely on what it is you need to do, and the constraints you are operating under. I have no idea what they are; I'm just saying that trying to hack an error condition into an in-line table-valued function seems like one of the least-preferred options to me. I don't think I would ever do it.
In particular a common case where we cannot make the Data Mart on its own meet two conflicting needs, would be where pediatric measurement is preferred in mm, but adult is preferred in cm.
@CadeRoux For example, I can imagine using a different type of module (stored procedure, multi-statement TVF etc.) or a wrapper of some kind, or returning an error code in an extra column, or...well, many other things I guess. Perhaps one concrete example in a question on the main site would be the way to go. It would, no doubt, be nice if functions were more, er, functional.
@PaulWhite OK. TVFs are by far the most functional when we need to expose things in views because the ultimate consumers will have difficulty consuming and combining non-tabular data sources in things like Tableau and PowerBI.
I want to generate reports from my SQL Server tables.
I have some already made stored procedures that I would like to use to generate reports from.
I haven't found a way to do so.
Only by rewriting the queries.
Thanks :)
It's fine, we're not sure we want to document and publicize that the users will now have this capability. We found a discrepancy in our system of an assumption that the units we were pulling from the source OLTP system were in the preferred unit so that we would not have to convert. Then we discovered that about 30% of them did need conversion, so we implemented conversion into the Data Mart so that people using it would have their preferred unit.
Along the way, we revealed that that is not uniformly accepted by all cardiologists (particularly differing in peds vs adult), but also by country.
Some things like the choice of symbol ml vs cc don't actually involve unit conversion, it's just nomenclature, but some like mm vs cm are unit conversion.
If the scope of our product went all the way to owning every dashboard in either Tableau/PowerBI or whatever, it wouldn't be an issue, because we could control everything, but since we want to expose things at the database level, it kinds of limits what we can do and how we can present it to their choice of tools.
It's fine. It's just trying to figure out what is best for them. If I return no rows or NULL rows because they CROSS APPLY or OUTER APPLY to convert with bad parameters, they will get a certain behavior, but if they get an error thrown, that's more likely to stop themselves shooting themselves in the foot.
@PaulWhite If the artificial column throwing the error is not requested in the SELECT, it gets optimized out, so yeah, not even enforceable to make them select that column.
