how to write a case in sql ?

Subin Thomas

7y1kSQL

select
case a.[RoleName] = 'student' then 'its student'
when a.[RoleName] = 'teacher' then 'its teacher'
when a.[RoleName] = 'princi' then 'its principal'
else 'attendant'
end from Roles]as a
inner join RoleDetails as b on
RoleId=b.RoleId
where b.Reserveid='18'

i am getting an error at 02,03,04 line at "="

error: incorrect syntax near =

whats wrong in this query if anyone can help me

thanks,

subin.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar7y ago
Accepted answer
you forgot to write when after case
please check below

case when a.[RoleName] = 'student' then 'its student'
when a.[RoleName] = 'teacher' then 'its teacher'
when a.[RoleName] = 'princi' then 'its principal'
else 'attendant'

Please accept the answer it helpful for you
Subin Thomas7y ago
thanks madan
Subin Thomas7y ago
its has so many tables and i dont have permission of taking a schema i am using only two tables roles and roledetails
Jenith Thakkar7y ago
Yes it's possible but for that i need to check schema of your table
Subin Thomas7y ago
i didnt got you jenith

i mean i have 3 panels consists of gridview and other info which the logic is written in a function code behind

each panel consist of students ,teachers and principal
if the user is student then student panel should be displayed if teacher then teacher panel should display and so on
so is it possible like above
Jenith Thakkar7y ago
Yes, you can do that in below way... GetByRoles is a function

select dbo.GetByRoles(a.[RoleName]) as BusinessDays from Roles]as a
inner join RoleDetails as b on
RoleId=b.RoleId
where b.Reserveid='18'
Subin Thomas7y ago
hey thanks jenith
and i had one question instead of passing 'its student ' after then can i call any function

actually i had to call functions if its a teacher/student/or any i was testing with this query
so can i call and if yes how can we do that
Madan Shekar7y ago
SELECT CASE title WHEN 'Carmelito Mendoza' THEN 'driver'
WHEN 'active' THEN 'status'
ELSE 'your wish'
END AS resultcolumn
FROM [dbo].[allocation]

// title is column name

your query i modified
select
case a.[RoleName] when 'student' then 'its student'
when 'teacher' then 'its teacher'
when 'princi' then 'its principal'
else 'attendant'
end from Roles as a
inner join RoleDetails as b on
RoleId=b.RoleId
where b.Reserveid='18'

if it helps please acccept the answer.