stored procedure style functionality - clarification needed (V2)
Hi,
I have a requirement to produce a result set for a dashboard based on parameters defined at run time.
As i see it, this is classic stored procedure territory, ie passing values to a procedure held in the db which dynamically generates a query and returns the result set to the caller.
However, having looked at this for a while, there doesn't appear to be a way/workaround to achieve this.
So far I've considered/discounted the following ...
a) User defined transform functions.
As I understand it, a UDTF is effectively a row level function, using the column values for each row in the result set collected by the calling query.
b) External procedures.
I've created a test external procedure which creates a view dynamically based on parameters passed to it in the calling query, however the calling report would need to call the extproc to generate a result set, and then query the view, ie 2 steps !
The only remaining option I can see is to look at our design again to try to avoid dynamically generating result sets, however, before doing that, I'd appreciate a steer as to whether I'm missing another option or if I'm wrong with any of the before.
Thanks,
Steve