CONDITIONAL CASE STATEMENT

I'm trying to write a conditional case statement, but I cannot work out the syntax. I am trying to get a list of tickets that are either open or closed and assigned to a UserID. This is governed by whether a Status has been set, and whether the status is open or closed. If no status has been set, then the ticket is open. Therefore I need to see whether the Status is IN or NOT IN a sub query of Closed Statuses. Here is my T-SQL:

ALTER PROCEDURE [dbo].[TicketsGetByAssignedToUserID]
      @AssignedToUserID int,
      @SortOrder nvarchar(50),
      @Closed bit
AS

SELECT
      [ID],
      [TicketNo],
      [AssignedToUserID],
      [Summary],
      [Description],
      [StatusID]
FROM .Tickets
WHERE
      AssignedToUserID = @AssignedToUserID
      AND CASE WHEN @Closed = 0 THEN [StatusID] NOT IN (SELECT [ID] FROM .Statuses WHERE [IsClosed] <> 0)
                        ELSE [StatusID] IN (SELECT [ID] FROM .Statuses WHERE [IsClosed] <> 0)
                      END
ORDER BY CASE WHEN @SortOrder = 'TicketNo' THEN [TicketNo] END ASC,
             CASE WHEN @SortOrder = 'StatusID' THEN [StatusID] END ASC,
             CASE WHEN @SortOrder = '' THEN [TicketNo] END  ASC

I'm getting:
Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near the keyword 'ORDER'.
leedurrantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
Try this

SELECT
      [ID],
      [TicketNo],
      [AssignedToUserID],
      [Summary],
      [Description],
      [StatusID]
FROM .Tickets
WHERE
      AssignedToUserID = @AssignedToUserID
      AND ( (@Closed = 0 And [StatusID] NOT IN (SELECT [ID] FROM .Statuses WHERE [IsClosed] <> 0)) OR
 ( @Closed<>0 And [StatusID] IN (SELECT [ID] FROM .Statuses WHERE [IsClosed] <> 0) )
ORDER BY CASE WHEN @SortOrder = 'TicketNo' THEN [TicketNo] END ASC,
             CASE WHEN @SortOrder = 'StatusID' THEN [StatusID] END ASC,
             CASE WHEN @SortOrder = '' THEN [TicketNo] END  ASC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot put WHERE conditions inside the CASE condition itself:
SELECT
      [ID],
      [TicketNo],
      [AssignedToUserID],
      [Summary],
      [Description],
      [StatusID]
FROM .Tickets
WHERE AssignedToUserID = @AssignedToUserID
AND (  (  @Closed = 0  AND [StatusID] NOT IN (SELECT [ID] FROM .Statuses WHERE [IsClosed] <> 0)
    OR (  @Closed <> 0 AND [StatusID] IN (SELECT [ID] FROM .Statuses WHERE [IsClosed] <> 0 )
    )
ORDER BY CASE WHEN @SortOrder = 'TicketNo' THEN [TicketNo] END ASC,
             CASE WHEN @SortOrder = 'StatusID' THEN [StatusID] END ASC,
             CASE WHEN @SortOrder = '' THEN [TicketNo] END  ASC

Open in new window

0
leedurrantAuthor Commented:
Wow, two perfect answers in under 5 minutes. To say that I am impressed is an understatement!!!!

Thank you both for your help. Unfortunately angellll, asvforce just beat you to it.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

leedurrantAuthor Commented:
Wow, two perfect answers in under 5 minutes. To say that I am impressed is an understatement!!!!

Thank you both for your help. Unfortunately angellll, asvforce just beat you to it.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no problem, glad you got your solution.

just to note (for the record :)
the difference of the suggestions was < 1 minute, and I took probably 10 seconds more just to type the additional note, which explains the "why"

CHeers
0
z3r3Commented:
You are using case in an unexpected way. The other experts' suggestions of changing the where clause will help you as you need to be specific in a where clause on how to get data. The case statement is probably handiest in the select portion when getting columns and in the order by. Just take note that in the order by section if there are differing data types then you need to end each case imediatly after the then as you have already done.
0
leedurrantAuthor Commented:
angellll, I take your points on board, and your help is much appreciated.

Looking at your profile, somehow I don't think that the 200 points matter a great deal, more the recognition that your answer was spot on, which it was, and for this I am truly gratefull.

Thank you again.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>angellll, I take your points on board,
thanks in advance, it's more for the "others" that I usually "beat"!

>Looking at your profile, somehow I don't think that the 200 points matter a great deal,
you are 100% correct :)

>more the recognition that your answer was spot on, which it was, and for this I am truly gratefull.
that's what counts for me.

CHeers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.