0

I am trying to capture sql server waits from dmv sys-dm-exec-requests in a stored procedure to run over time.

Later i display that in UI as a view. Currently it returns over 30 columns from the proc created which is simply selecting the data from that view.

How can i add a parameters, lets say which gives me flexibility to also make a choice in which columns user might be interested to see on dashboard?

Newbie-DBA
  • 107
  • 5
  • 1
    This should be defined in the report, *not* the procedure. You can't parametrise the columns that are returned from a dataset; it must be well defined. If you "had" to, you would need to use dynamic SQL to construct the query with the relevant columns; I strongly recommend against this. Use your reporting tool to define the visibility of the columns in the application based on the parameters. – Thom A Sep 03 '21 at 13:57
  • @Larnu: Thanks for your comment. Here the dashboard is Grafana and i checked there is no option to filter in data set like in SSRS. Therefore was looking how i can get this done in SP itself – Newbie-DBA Sep 03 '21 at 14:55
  • 1
    Like I said, I *strongly* recommend against the idea; you would have to use Dynamic SQL. As a self declared Newbie, that is not a path you should be going down yet. – Thom A Sep 03 '21 at 15:01
  • @Newbie-DBA Sorry if you've already looked at this (I'm not familiar with Grafana) but is this what you need to do your filtering client-side? https://grafana.com/docs/grafana/latest/variables/variable-types/add-ad-hoc-filters/ Filtering is a basic reporting feature, it would really surprise me if Grafana doesn't support it. – squillman Sep 03 '21 at 15:17
  • @squillman- Yes i checked but as mentioned ":Ad hoc filter variables only work with Prometheus, Loki, InfluxDB, and Elasticsearch data sources." – Newbie-DBA Sep 03 '21 at 15:57

0 Answers0