CASE in an Update Stored Procedure

[RobS23]

I think it should be pretty clear, but...

I'm updating a date field with the value of the @Date parameter. Which field I update is dependent upon the value of the status parameter.


UPDATE tblVenueDates
CASE
WHEN @VenueStatusID = 1 THEN
SET fldNominatedDate = @Date

WHEN @VenueStatusID = 2 THEN
SET fldSurveyDate = @Date

WHEN @VenueStatusID = 3 THEN
SET fldLiveDate = @Date

WHEN @VenueStatusID = 4 THEN
SET fldRefusedDate = @Date

WHEN @VenueStatusID =5 THEN
SET fldRemovedDate = @Date

END

WHERE fldVenueID = @VenueID


Hope you can work it out 'cause I can't!!!

Cheers

 

[rajeevnandanmishra]

Hi there,
If you insist on the use of CASE then following is the code
------------------------
UPDATE tblVenueDates
SET fldNominatedDate =
CASE WHEN @VenueStatusID = 1 THEN @Date
ELSE fldNominatedDate END,
fldSurveyDate =
CASE WHEN @VenueStatusID = 2 THEN @Date
ELSE fldSurveyDate END,
fldLiveDate =
CASE WHEN @VenueStatusID = 3 THEN @Date
ELSE fldLiveDate END,
fldRefusedDate =
CASE WHEN @VenueStatusID = 4 THEN @Date
ELSE fldRefusedDate,
fldRemovedDate =
CASE WHEN @VenueStatusID =5 THEN @Date
ELSE fldRemovedDate END
WHERE fldVenueID = @VenueID
------------------------

Otherwise, if i would have this situation i prefer following code.
-----------------
DECLARE @sql VARCHAR(200)
SELECT @sql='UPDATE tblVenueDates SET '+
CASE WHEN @VenueStatusID = 1 THEN ' fldNominatedDate '
WHEN @VenueStatusID = 2 THEN ' fldSurveyDate '
WHEN @VenueStatusID = 3 THEN ' fldLiveDate '
WHEN @VenueStatusID = 4 THEN ' fldRefusedDate '
WHEN @VenueStatusID = 5 THEN 'fldRemovedDate '+
"= '"+CONVERT(CHAR(10),@Date,102)+"' "+
'WHERE fldVenueID = '+CONVERT(CHAR(10),@VenueID)
EXEC(@sql)
-----------------


Hope it will help you!