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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With