Case statement problem 1

[aaronjonmartin]

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:

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)

 

[vongrunt]

What if for example both @Res1 and @Res2 are 'ca'?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[aaronjonmartin]

thanks vongrunt, stupidly i didnt even think of that. Hmmm looks like i will have to find another way of doing this.

&quot;It's so much easier to suggest solutions when you don't know too much about the problem.&quot;
Malcolm Forbes (1919 - 1990)

 

[vongrunt]

Lemme check... if any of @ResN values is 'ca' corresponding PredN column must be excluded from search, right?

...
WHERE 
	(@Res1 = 'ca' OR @Res1 = Pred1) AND
	(@Res2 = 'ca' OR @Res2 = Pred2) AND
	(@Res3 = 'ca' OR @Res3 = Pred3) AND
	(@Res4 = 'ca' OR @Res4 = Pred4) AND
	(@Res5 = 'ca' OR @Res5 = Pred5)
...

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[aaronjonmartin]

yeah vongrunt that is exactly write. If any of the @ResN variables contain the string 'ca' then they should be excluded from the matching.

Do you think that code you have added would do the job?

&quot;It's so much easier to suggest solutions when you don't know too much about the problem.&quot;
Malcolm Forbes (1919 - 1990)

 

[vongrunt]

Yup, this is standard way to handle 'optional' arguments.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[aaronjonmartin]

so i wouldnt need the case statement after all? that block of code would just completely replace the case statment in my original code above?

&quot;It's so much easier to suggest solutions when you don't know too much about the problem.&quot;
Malcolm Forbes (1919 - 1990)

 

[vongrunt]

Yup again.

How it works: take a look at this:

(@Res1 = 'ca' OR @Res1 = Pred1)

If @Res = 'ca' then this part always returns TRUE. Since TRUE OR anything always returns TRUE (Boolean algebra basics), second part of expression (@Res1 = Pred1) doesn't matter. Just like it isn't specified at all...

Parenthesis () are necessary because OR has different precedence than AND.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[aaronjonmartin]

ok thanks vongrunt, i really apprecite your help.

&quot;It's so much easier to suggest solutions when you don't know too much about the problem.&quot;
Malcolm Forbes (1919 - 1990)

 

[aaronjonmartin]

worked a treat mate, thankyou very much

&quot;It's so much easier to suggest solutions when you don't know too much about the problem.&quot;
Malcolm Forbes (1919 - 1990)