stored procedure style functionality - clarification needed (V2)



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.






Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file