November 30, 2016 at 11:32 am
I have a stored procedure that results in a average and median length of stay(LOS) for our emergency room. My problem is.........the data source for the metrics includes parameters to limit the data used to produce the LOS data. For example I need a parameter for different working shifts so, I can choose "7-3" or "3-11". The user also needs to be able to run the data for all shifts. Is there a way to "ignore" parameters? Any thoughts?
November 30, 2016 at 1:02 pm
You can usually optionally ignore parameters by adding in something like "OR @param IS NULL" which returns all results. If you have multiple parameters that can be NULL, though, you can get what Gail Shaw calls Catch-All-Queries[/url], which can be a performance issue. In your instance, a rough pseudo-code might look like:
WHERE (@param IS NULL
OR (@param = 7 AND SomeTimeField >= 7 AND SomeTimeField < 3)
OR (@param = 3 AND SomeTimeField >= 3 AND SomeTimeField < 11))
If the user chooses 7, they get the results for the 7-3 shift; if they choose 3 they get the 3-11 shift results; and if they choose NULL they get all the results.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply