I have an event table in which there is an author field and a leader field. A person from the table of my face can be either the author or the host of the same event, or they can be either the host or the author. I need to apply filters to a result set based on their face ID and the type or filter that they select. I have filters:
All: This returns any entries in which they are either the author or the presenter.
AllPresenter: All entries as host.
AllAuthor: all entries as an author.
PresenterOnly: Records only as a presenter, not an author.
AuthorOnly: Writes only as an author, not a presenter.
PresenterAndAuthorOnly: All entries in which they are the presenter and author.
I currently have a stored proc that uses external ifs like the one below, and I tried to find a way to combine all these similar select statements into one. I was not lucky to find a better solution, and I wonder if I have enough equipment.
If (@filter = 'PandAOnly' or @filter = 'AllP' or @filter = 'AllA') begin Select * from Event Where PresenterId = Case @personId is null then PresenterId else @personId end and AuthorId = Case @personId is null then AuthorId else @personId end end else if (@filter = 'All') begin Select * from Event Where PresenterId = @personId Or AuthorId = @personId end else if (@fitler = 'POnly') begin Select * from Event Where PresenterId = @personId and AuthorId <> @personId end else begin Select * from Event Where AuthorId = @personId and PresenterId <> @personId end
source share