Geek Answers Handbook

Sql Multiple Cases

Hey. I am trying to write several case statements to set the priority of a job. There are several tables and identifiers that participate in this, but basically I want to check if the work exists, if it exists, if the question related to it is 80, and then has several status elements, so if the junior category of tasks is 1, The answer will be the identifier for High. The code I have done so far can 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 statement that sets @MinorCategoryID WHEN 91THEN 130, etc.

I 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 is appreciated - sorry if it is not clear, I have a spotty fuzzy head!

+4
source share
3 answers

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 ​​() with the button

 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
+13
source

Try:

 CASE WHEN @MinorCategoryID = 87 THEN 129 ELSE NULL END

Obviously, replace NULL in the case of ELSE with a different value if necessary.

0
source

Try creating a list table for these possible scenarios. Therefore, when new cases appear, you need to update this 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
0
source

More articles:


All Articles