Help with CASE Statement in Stored Proc
-
Ok, can someone please tell me what is wrong with this code?
Case @PayPeriod
--When 1-- Weekly
--When 2-- Bi Weekly
When 3 Then-- Semi Monthly
BEGIN
Case (DatePart(d, @ActivityDate)
When <= 15
BEGIN
Set @StartDate = Cast((Cast(DatePart(m, @ActivityDate) As VarChar) + '/01/' + Cast(DatePart(yyyy, @ActivityDate) As VarChar)) As SmallDateTime)
Set @EndDate = Cast((Cast(DatePart(m, @ActivityDate) As VarChar) + '/15/' + Cast(DatePart(yyyy, @ActivityDate) As VarChar)) As SmallDateTime)
END
Else
BEGIN
Set @StartDate = Cast((Cast(DatePart(m, @ActivityDate) As VarChar) + '/16/' + Cast(DatePart(yyyy, @ActivityDate) As VarChar)) As SmallDateTime)
Set @EndDate = Cast((Cast(DatePart(m, @ActivityDate) As VarChar) + '/01/' + Cast(DatePart(yyyy, @ActivityDate) As VarChar)) As SmallDateTime)
Set @EndDate = DateAdd(d, -1, DateAdd(m, 1, @EndDate))
END
End
END
--When 4-- Monthly
--When 5-- Quarterly
--When 6-- Semi Annually
--When 7-- Annually
End-- End Case -
Whoops, I put this in the wrong topic section!
-
CASE should go in a SELECT statement. In other words, either change your statement to a SELECT, or change your case statements to an IF block. Remember, CASE is a function, IF is a language construct. The former you can use in a SELECT, the latter you can't.
-
Well, that is not helpful
. I ended up changing to "IF..Then" but seems like there should be a Select Case or something...oh well. -
quote:
Well, that is not helpful. I ended up changing to "IF..Then" but seems like there should be a Select Case or something...oh well.Not quite sure what you are talking about. T-SQL is not VB. It is predominantly a set-based language designed for data retrieval based on set conditions, not procedureal programming expressions. There is no such thing as an IF ... THEN in SQL. I think you might be used to Access? In actuality, the CASE function is much more flexible than you are giving it credit for. Look in Books OnLine under "Using CASE" for examples of its correct use.
-
Right, I already knew how to use CASE in a SQL Statement, I've done this for CrossTab Reports. Anyway, it just seems like there should be something equivalent to a Case statement for things like User Defined Functions and such...it really cuts down on code and make it easier to read. But like you said, it's just not designed for that.
. I ended up changing to "IF..Then" but seems like there should be a Select Case or something...oh well.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply