Case Expression in Where Claus

I there a way to differ the filtering field through a Case expression in the Where claus?

ex:

Where
Case
When @.StuNum = ''
Then S.SSN = @.SSN
Else
S.StuNum = @.StuNum
End

And ...

The actual field to filter by differs, but I can't seem to find the right syntax to do this.

Any help is appreciated : )

The answer to your question is yes... But it is unwieldy. Try this instead:

WHERE (@.StuNum='' ANDS.SSN=@.SSN) OR (@.StuNum<>'' AND S.StuNum=@.StuNum)

|||

Hi,

I am not sure what you want... so I am sending my answer on some guess :) if it is not the thing you are looking for then please explain your problem a bit more. Anyways please find the query below.

DECLARE @.EmpIDINT

SET @.EmpID=2

SELECT*

FROM HumanResources.Employee

WHERE

(SELECTCASE

WHEN @.EmpID=1THEN

ContactID

ELSE

NationalIDNumberEND)=(SELECTCASE

WHEN @.EmpID=1THEN

1209

ELSE

253022876END)

Hope it Helps!

Bhaskar!

|||

I think you hit the nail on the head. I have my syntax listed as below now. Seems the "=" sign was being interpreted as part of the Case statement and needed to be separated out. Thanks to both of yo for your input.

Where

(Case

When (@.StuNum = ' ' or @.StuNum = '' or @.StuNum = null)

Then S.SSN

Else

S.StuNum

End) = (Case

When (@.StuNum = ' ' or @.StuNum = '' or @.StuNum = null)

Then @.SSN

Else

@.StuNum

End)