How to refactor the following sql statement?

Question

Declare @IsExistRoleAR As bit
Declare @IsExistRoleEN As bit
Declare @IsExist AS bit
set @IsExistRoleAR=(SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
                    FROM Roles
                    WHERE RoleDescAR='Arabic Name') 

set @IsExistRoleEN=(SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
FROM Roles
WHERE RoleDescEN='English Name') 

set @IsExist=(@IsExistRoleAR | @IsExistRoleEN)
select @IsExist

Answer 1

You could just use OR:

  SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
  FROM Roles
  WHERE RoleDescAR='Arabic Name' OR RoleDescEN='English Name'

Answer 2

You don't really need a CASE statement here, unless I've missed something. (Always possible.)

select count(RoleID)
from roles
where RoleDescEN = 'EnglishName'
   or RoleDescAR = 'ArabicName';

---

If you're trying to identify persons who have those roles, you might be looking for something along these lines.

create table roles (
  RoleID integer primary key,
  RoleDescEN varchar(30) ,
  RoledescAR varchar(30) 
  );

insert into roles values
(1, 'EnglishName', null),
(2, null, 'ArabicName'),
(3, 'EnglishName', null),
(4, 'EnglishName', null),
(5, null, 'ArabicName'),
(6, null, 'ArabicName'),
(7, null, 'OtherName');


create table persons (
  person_id integer primary key,
  person_name varchar(35) not null,
  person_role integer references roles (RoleID)
  );

insert into persons values
(1, 'Obama, Barak', 1),
(2, 'Obama, Michelle', 2),
(3, 'Biden, Joe', 7);

I would not be surprised if my tables don't match yours. In your question, always include CREATE TABLE statements and INSERT statements to get the best answers.

select *
from persons p
inner join roles r
        on r.RoleID = p.person_role
where RoleDescEN = 'EnglishName'
   or RoleDescAR = 'ArabicName';

Answer 3

Since you haven't accepted any of the other answers, I'm going to take a guess that perhaps you want more information back:

SELECT CASE
    WHEN EXISTS (SELECT * from Roles Where RoleDescAR='Arabic Name') then 'AR'
    WHEN EXISTS (SELECT * from Roles Where RoleDescEN='English Name') then 'EN'
    ELSE 'none' END As whichExists

Answer 4

Select Case When exists
   (Select * From Roles
    WHERE RoleDescAR='Arabic Name' Or 
          RoleDescEN='English Name')
  Then 1 Else 0 End

If you want to know the names of the people who meet this criteria, that's a completely different question, then you need to provide the schema for the table that has one row per each person, and the roleId, and then, (assuming that table is named Person) Try:

  Select Name
  From Person p
  Where exists
   (Select * From Roles
    Where RoleId = p.RoleId
        And RoleDescAR='Arabic Name' Or 
            RoleDescEN='English Name')