February 5, 2014 at 6:15 am
I'm working on a report that shows the outcomes of certain activities. The report was built some time ago and shows the end users everything they need at once. The report currently shows, along with other information, the person that carried out the activity and who the activity was reported to on the same row. What they'd like to see is all the activities carried out by an individual OR all the activities reported to an organisation OR everything.
For example,Anne Bonney and Blackbeard both raid Kingston and this is reported to London while Captain Kidd raids Kingston and this is reported to Bristol. The end user would like to have the option of filtering the report to see what each pirate did OR which pirate was reported to which city OR to see all the activity.
I know I can use parameters and code to allow nulls but the proc used to produce this report is pretty involved and short of a re-write that will be difficult. Is there any way I can filter the visible rows in the report to show those that match parameter x, or those that match parameter y, or everything if no parameters are provided.
If anybody needs any more info just ask.
I do intend to re-do the proc as soon as I can but it's likely to take some time I don't necessarily have. As I said it provides everything that's required but it's slow to run and in my opinion, pretty inefficient.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 5, 2014 at 6:39 am
Your easiest solution might be to do this simple change. If a parameter is given a value, then a subset of rows will be returned. If the parameter is null, you'll get all rows.
where ColumnA = isnull(@ParameterA, ColumnA)
Another possible solution is to execute into a table variable or temp table:
declare @t table (colA int, colB int);
insert into @t
execute reportproc parmA, parmB
February 5, 2014 at 8:10 am
Bill Talada (2/5/2014)
Your easiest solution might be to do this simple change. If a parameter is given a value, then a subset of rows will be returned. If the parameter is null, you'll get all rows.where ColumnA = isnull(@ParameterA, ColumnA)
Another possible solution is to execute into a table variable or temp table:
declare @t table (colA int, colB int);
insert into @t
execute reportproc parmA, parmB
Thanks Bill, I went with your first option and it worked a treat. It turned out I could fit parameters into the proc much more easily than I first thought. It's still on the schedule for a re-write though.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply