Alexandria Alexandria - 10 months ago 43
SQL Question

if statement in Stored procedure

I am trying to work out the best way to do this problem.

I have a database table for teaching activities done in a university, the university has a few campuses were teaching occurs I am trying to show a gridview for each campus based on a drop down list. I have to update the activities table field CampusOccurred based on the room. Rooms are EMG12, OG114, DS15.

If room start with:-

E - Earn Campus

O - Oden Campus

D - Donen Campus

My idea at the moment is:-
For each row in Activites
If Room MyString.ToCharArray()[0] = E
Then Update CampusOccurred = Earn
Else If Room MyString.ToCharArray()[0] = O
Then Update CampusOccurred = Odean

I can do the rest myself displaying drop down list and gridview but I'm just not sure the best way of going about the above. Is it possible to do If statements in SQL?

Answer Source

If I understand your request correctly, you want a case statement in update:

update Activities
set CampusOccured = case left(Room,1) 
                        when 'E' then 'Earn'
                        when 'O' then 'Oden'
                        when 'D' then 'Donen'