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 = @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)
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
Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near the keyword 'ORDER'.