CODEWITHSUNDEEP
sqlsql-server
Jesse Jaime
Jesse Jaime

Reputation: 344

SQL Select Statement Where

I have an event table that has an author field and a presenter field. A person from my person table can be both an author and a presenter of the same event or they can be either the presenter or the author. I am needing to apply filters to the result set based on their person ID and the type or filter they have selected. The filters I have are:

All: This returns any records in which they are either an author or presenter.

AllPresenter: all records as presenter.

AllAuthor: all records as author.

PresenterOnly: only records as presenter and not author.

AuthorOnly: only records as author and not presenter.

PresenterAndAuthorOnly: all records in which they are the presenter and the author.

I currently have a stored proc that uses outer ifs like the one below and I have tried to find a way to combine all of these similar select statements into one. I've not had much luck finding a better solution and I am wondering if I am missing a technique.

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

Upvotes: 6

Views: 80

Answers (1)

M.Ali
M.Ali

Reputation: 69494

Select * from Event 
Where 
   (
        ((@personId is null) OR (PresenterId =@personId ))
        and 
        ((@personId  is null) OR (AuthorId = @personId))
        AND 
        (@filter = 'PandAOnly' or @filter = 'AllP' or @filter = 'AllA')
    )
OR 
  (
       (PresenterId = @personId 
        Or
        AuthorId = @personId )
    AND (@filter = 'All')
  )
OR 
  (
       PresenterId = @personId 
       and
       AuthorId <> @personId 
       and 
       @fitler = 'POnly'
  )
OR 
 (
        AuthorId = @personId 
        and 
        PresenterId <> @personId 
       and 
       @fitler = 'AOnly'
 )

NOTE

I would rather stick to the stored procedure, the execution plan for the above query will be scary :)

Upvotes: 5

Related Questions