# How to refactor the following sql statement?

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


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'

• I want to know who is who was exists at table Roles "Arabic Field" Or English Field? I don't know how to explain that
– Mohammed Qamhawi
Jun 10 '13 at 15:43
• Add columns to select statement like: SELECT RoleDescAR, RoleDescEN, CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
– Garath
Jun 10 '13 at 15:49

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);


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

• I want to know who is who was exists at table Roles "Arabic Field" Or English Field? I don't know how to explain that
– Mohammed Qamhawi
Jun 10 '13 at 15:45
• I think you mean, "I want to know the names of everyone who has either the role 'Arabic Name' or the role 'English name'." (I could easily be wrong about that.) The code you posted makes no attempt to do that, and it doesn't suggest that there might be other tables involved. Edit your question, and paste the CREATE TABLE statements from the relevant tables. Jun 10 '13 at 15:48

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

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

• I want to know who is who was exists at table Roles "Arabic Field" Or English Field? I don't know how to explain that
– Mohammed Qamhawi
Jun 10 '13 at 15:45