help on case/if in sp

SQL Apprentice Question
I am giving a section of code of an SP I am working on and I believe I
can get help with this as it seems simple yet hard for me to
accomplish.


Here is the portion of the code starting in the "where" clause of the
sp.


where Case when @period=Monthly then
left(a.Claim_Date,2) =@mm and right(a.Claim_Date,2)=@yy
when @period=DateRange then
substring(a.Claim_Date,3,2)+substring(a.Claim_Date,1,2) between @from
and @to
end


claim date in the database is char and is represented as mmyy.


When I use this Case statement in the sp, I get error sg 102, Level 15,
State 1,
Incorrect syntax near '='


But if I take out any case statement and simply say:


where left(a.Claim_Date,2) =@mm and right(a.Claim_Date,2)=@yy and ....
that works fine.


Could you please tell me how I can fix the above portion with Case or
if statement?


If you need more info, I will furnish. But I thought it would be easy
to fix the Case portion of the code without seeing more.


I really do appreciate your help and suggestions.


Thanks a million in advance.



Celko Answers
>> claim date in the database is char and is represented as mmyy.<<


WHY? This is not a date at all. You do not understand temporal data
types.

This predicate should be no more complex than:


WHERE claim_date BETWEEN @from_date AND @to_date


assuming you have proper DDL.



>> When I use this Case statement in the sp, I get error sg 102, Level 15,State 1, Incorrect syntax near '=' <<


There is no CASE statement in SQL; we do have a CASE expression.
Expressions return scalar values, not control flow. You are trying to
do COBOL-style string manipulations in SQL instead of actually writing
SQL in SQL.