how to write a case in sql ?

Question

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.

Jenith Thakkar · 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

Madan Shekar · Answer

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.

Subin Thomas · Answer

thanks madan

Subin Thomas · Answer

its has so many tables and i dont have permission of taking a schema i am using only two tables roles and roledetails

Jenith Thakkar · Answer

Yes it's possible but for that i need to check schema of your table

Subin Thomas · Answer

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 Thakkar · Answer

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 Thomas · Answer

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