stored procedure style functionality - clarification needed (V2)

stevejj

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