aaronjonmartin
Technical User
Ive been looking at some example case statement syntax on a few websites, but every example i have seen has the case statement directly after the SELECT of a query. I wish to use CASE after the WHERE section of a query, is this possible? or is it not possible to do this and that is why i have not seen any example code like this?
Here is the code i have been trying:
when i error check this code in query analyser (click the tick button) i get the following error:
Server: Msg 170, Level 15, State 1, Procedure SPinsertResults2, Line 17
Line 17: Incorrect syntax near '='.
where line 17 i think is the first line of the CASE statement.
If anyone who can give me some help with this it would be most appreciated.
Aaron
"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)
Here is the code i have been trying:
Code:
create procedure SPinsertResults2
@GameID int,
@Res1 char(2),
@Res2 char(2),
@Res3 char(2),
@Res4 char(2),
@Res5 char(2),
@NumGoals int
AS
INSERT INTO Results(GameID, Res1, Res2, Res3, Res4, Res5, ActualGoals)
VALUES (@GameID, @Res1, @Res2, @Res3, @Res4, @Res5, @NumGoals)
SELECT UserName, NumGoals FROM vUserPreds
WHERE
CASE WHEN @Res1='ca' THEN
Pred2=@Res2 AND Pred3=@Res3 AND Pred4=@Res4 AND Pred5=@Res5
WHEN @Res2='ca' THEN
Pred1=@Res1 AND Pred3=@Res3 AND Pred4=@Res4 AND Pred5=@Res5
WHEN @Res3='ca' THEN
Pred1=@Res1 AND Pred2=@Res2 AND Pred4=@Res4 AND Pred5=@Res5
WHEN @Res4='ca' THEN
Pred1=@Res1 AND Pred2=@Res2 AND Pred3=@Res3 AND Pred5=@Res5
WHEN @Res5='ca' THEN
Pred1=@Res1 AND Pred2=@Res2 AND Pred3=@Res3 AND Pred4=@Res4
ELSE
Pred1=@Res1 AND Pred2=@Res2 AND Pred3=@Res3 AND Pred4=@Res4 AND Pred5=@Res5
END
GO
when i error check this code in query analyser (click the tick button) i get the following error:
Server: Msg 170, Level 15, State 1, Procedure SPinsertResults2, Line 17
Line 17: Incorrect syntax near '='.
where line 17 i think is the first line of the CASE statement.
If anyone who can give me some help with this it would be most appreciated.
Aaron
"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)