if statement in Stored procedure

Question

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?

http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select — Alexan, Oct 06 '16 at 14:12
What does [tag:c#] have to do with the question posed? I'm assuming you wanted to do this in a stored procedure based on the question title — Kritner, Oct 06 '16 at 14:13
Tag the dbms you're using. Only a few dbms products have ANSI SQL compliant stored procedures. — jarlh, Oct 06 '16 at 14:14
That is not particularly a good idea. What happens if they add another campus named "Online Campus" <--- just an example but plausible with lots of school going online. Then your code will fail. — sam yi, Oct 06 '16 at 14:14
@Alexandria the thread linked still applies. It's the principle, not the literal of the content in the linked question — Kritner, Oct 06 '16 at 14:14
The data model is bad. You should have a room table and a campus table and a room record would be linked to a campus record, rather than applying the "secret of the first character". — Thorsten Kettner, Oct 06 '16 at 14:16
@ThorstenKettner I know its bad but its another system that is being used which cant be changed so I have to accommodate it. — Alexandria, Oct 06 '16 at 14:19
@Kritner Sorry I just wasnt sure if I could do it as stored procedure, if i want to do it now myself I would use c# and if statments to pass paramater to sql update query. — Alexandria, Oct 06 '16 at 14:21
So you have a drop down list and based on which entry a user Selects, you want to show data in a grid. What is in that drop down list? The rooms? The campuses? Something else? — Thorsten Kettner, Oct 06 '16 at 14:23
Do you want to update an existing table with new data or do you want to fetch data from stored procedure based on a parameter? — Gasper, Oct 06 '16 at 14:28

score 2 · Answer 1 · answered Oct 06 '16 at 14:19

2

You can write a SQL query that does something like this:

select . . .,
       (case when left(Room, 1) = 'E' then 'Earn Campus'
             when left(Room, 1) = 'O' then 'Oden Campus'
             when left(Room, 1) = 'D' then 'Donen Compus'
        end) as Campus

If you fetch the data this way from the database, then the work is done for you.

Note: Most but not all databases support left(). All support a function that is equivalent.

answered Oct 06 '16 at 14:19

Gordon Linoff

1,242,037
58
646
786

Sorry i dont understand how this works. Do i do a update statement after this. – Alexandria Oct 06 '16 at 14:41
@Alexandria . . . If you just need the data in a grid view, a `select` should be sufficient. You don't need to actually modify the data. – Gordon Linoff Oct 06 '16 at 23:45

Gasper · Accepted Answer · 2016-10-06T16:16:08.533

2

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'
                    end

edited Oct 06 '16 at 16:16

answered Oct 06 '16 at 14:25

Gasper

2,752
1
12
16

Thats exactly what im trying to do, will give this a try now. Thanks so much :) – Alexandria Oct 06 '16 at 14:32
@Alexandria Glad to help. Please, mark the question as answered if it works for you. – Gasper Oct 06 '16 at 14:33
Its giving me an error when i try and run it. This is what im doing. CREATE PROCEDURE [dbo].[CampusOccurred] AS BEGIN update Activities set Activities.CampusOccurred = (case left(Room,1) when 'E' then 'Earn' when 'O' then 'Oden' when 'D' then 'Donen') END – Alexandria Oct 06 '16 at 14:35
Just wont run, like there is something missing at the end. – Alexandria Oct 06 '16 at 15:42
@Alexandria It's missing `end` for `case`. Try this: `CREATE PROCEDURE [dbo].[CampusOccurred] AS BEGIN update Activities set Activities.CampusOccurred = (case left(Room,1) when 'E' then 'Earn' when 'O' then 'Oden' when 'D' then 'Donen' end) END` – Gasper Oct 06 '16 at 16:23
Thanks that works perfectly. So just to understand, left is taking the first character ont he left then updating campusoccured based on that? Just one last thing, I have some NULL data in Room, is it possible if room is NULL then select data in home field and update campusOccurred from home field? – Alexandria Oct 07 '16 at 15:04
@Alexandria Yes, [`left`](https://msdn.microsoft.com/en-us/library/ms177601.aspx) is taking characters from the left of first argument. For `null` values you may use `else` part of case, see [example](https://msdn.microsoft.com/en-us/library/ms181765.aspx), or `when null then ...` and then statement for taking data from home field. – Gasper Oct 07 '16 at 15:21

if statement in Stored procedure

2 Answers2