sql Multiple case Statement

Hi I'm trying to write multiple case statements to preset the priority of a job. There are a few tables and ID s that are involved in this but basically I want to check whether the job exists if it does if the questionid related to it is 80 and then have multiple case statemnts so that if the jobs minor category is 1 then the answer will be the id for High. The code i ve done so far may explain better...

 ALTER Procedure [dbo].[usp_CreatePresetPriority]
   @HelpdeskID int,
   @MinorCategoryID int
 As
 BEGIN

IF EXISTS(SELECT * FROM 开发者_开发知识库TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
    BEGIN
        UPDATE TicketInformation
        SET AnswerInput = Null, 
            AnswerID = CASE @MinorCategoryID WHEN 87 THEN 129 END
        WHERE TicketID = @HelpdeskID And QuestionID = 80

    END

ELSE
    BEGIN
        INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
        VALUES (@HelpdeskID, 80, CASE @MinorCategoryID WHEN 87 THEN 129 END, Null)
    END

So this works but only for one option - where @MinorCategoryID = 87 I want to have more than 1 statements that set @MinorCategoryID WHEN 91THEN 130 etc...

I have tried...

    IF EXISTS(SELECT * FROM TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
 UPDATE TicketInformation SET AnswerInput = Null, AnswerID = CASE @MinorCategoryID WHEN 87 THEN 129 WHERE TicketID = @HelpdeskID And QuestionID = 80
ELSE INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput) VALUES (@HelpdeskID, 80, CASE @MinorCategoryID WHEN 87 THEN 129 END, Null)



     IF EXISTS(SELECT * FROM TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
 UPDATE TicketInformation SET AnswerInput = Null, AnswerID = CASE @MinorCategoryID WHEN 91 THEN 130 WHERE TicketID = @HelpdeskID And QuestionID = 80
ELSE INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput) VALUES (@HelpdeskID, 80, CASE @MinorCategoryID WHEN 91 THEN 130 END, Null)

Any help appreciated - sorry if its not clear i've got a friday fuzzy head!

---

You can assign it to a variable and then just insert the variable.

ELSE
    BEGIN

        SELECT @MinorCategoryID = CASE
               WHEN @MinorCategoryID = 87 THEN 129
               WHEN @MinorCategoryID = 91 THEN 130
            -- more cases here
            ELSE NULL 
        END
        INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
        VALUES (@HelpdeskID, 80, @MinorCategoryID, Null)
    END

or change the values() by a select

ELSE
    BEGIN                
        INSERT TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
        SELECT @HelpdeskID, 
               80, 
               CASE 
                   WHEN @MinorCategoryID = 87 THEN 129
                   WHEN @MinorCategoryID = 91 THEN 130
                   -- more cases here
                   ELSE NULL 
                END, 
                Null
    END

---

Give this a try:

CASE WHEN @MinorCategoryID = 87 THEN 129 ELSE NULL END

Obviously, replace the NULL in the ELSE case with another value if needed.

---

Try creating a list table for those possible scenarios. So when new cases pop up all you have to do is update that table.

Here is the code

if(OBJECT_ID('_MinorCategoryList') > 0)
    drop table _MinorCategoryList

create table _MinorCategoryList(
id int identity(1,1),
[when] int,
[then] int
)

insert into _MinorCategoryList ([when],[then])
values (87,129),(91,130)

select * from _MinorCategoryList

go

alter Procedure [dbo].[usp_CreatePresetPriority]
   @HelpdeskID int,
   @MinorCategoryID int
 As
 BEGIN

IF EXISTS(SELECT * FROM TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
    BEGIN
        UPDATE TicketInformation
        SET AnswerInput = Null, 
            AnswerID = (SELECT [then] FROM _MinorCategoryList WHERE [when] = @MinorCategoryID)
        WHERE TicketID = @HelpdeskID And QuestionID = 80

    END

ELSE
    BEGIN
        INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
        VALUES (@HelpdeskID, 80, (SELECT [then] FROM _MinorCategoryList WHERE [when] = @MinorCategoryID), Null)
    END
END

go