And and OR

ALTER procedure [dbo].[Sp_Recruit_Job]
@callval int=0,
@JobCode int=0,
@Position varchar(20)='',
@JobDesc varchar(MAX)='',
@PostStreamId varchar(10)='',
@GraduQuali varchar(10)='',
@Ten2StreamId varchar(10)='',
@StreamId varchar(10)='',
@ReqExp varchar(15)='',
@Salary varchar(25)='',
@JobLocation varchar(15)='',
@PostDate datetime='',
@EndDate datetime='',
@c_f_name varchar(15)='',
@c_m_name varchar(15)='',
@c_l_name varchar(15)='',
@email varchar(30)='',
@personal_id int=0,
@applyId int=0,
@dtInterview int=0,
@TimeReq int=0,
@ExpSalary decimal(12,2)=0,
@Resume varchar(max)='',
@howdid int=0,
@applydate datetime='',
@prof_qual varchar(10)='',
@prof_stream varchar(24)='',
@prof_year char(4)='',
@grad_qual varchar(10)='',
@grad_stream varchar(15)='',
@grad_year char(4)='',
@assignment1 int=0,
@assignment2 int=0,
@assignment3 int=0,
@assignment4 int=0,
@assignment5 int=0,
@ex_yyyy int=0,
@emp_gross decimal(12,2)=0





as
begin

DECLARE
@retrun_val VARCHAR(50) 
BEGIN 
TRAN



if @callval=1
begin



select rp_tbl_personal_details.c_f_name + '' +c_m_name + ''+ c_l_name  as name,
rp_tbl_academic_details.prof_qual ,rp_tbl_academic_details.grad_qual,
rp_tbl_academic_details.prof_stream,rp_tbl_academic_details.prof_year,
rp_tbl_academic_details.grad_stream,rp_tbl_academic_details.grad_year,
rp_tbl_professional.ex_yyyy,

rp_tbl_family.assignment1,rp_tbl_family.assignment2,rp_tbl_family.assignment3,rp_tbl_family.assignment4,rp_tbl_family.assignment5,

rp_tbl_professional.emp_gross,rp_tbl_CreateNewJob.JobCode,rp_tbl_CreateNewJob.Position,
rp_tbl_ApplyforJob.email,rp_tbl_ApplyforJob.TimeReq,rp_tbl_ApplyforJob.ExpSalary,
dtInterview =  case rp_tbl_ApplyforJob.dtInterview
when 1 then 'Any Day'
when 2 then 'Week Day'
when 3 then 'Weekends'
else 'Other'
end

from rp_tbl_CreateNewJob inner join rp_tbl_ApplyforJob 
on rp_tbl_CreateNewJob.JobCode=rp_tbl_ApplyforJob.JobCode
inner join rp_tbl_personal_details on
rp_tbl_ApplyforJob.email=rp_tbl_personal_details.email
inner join rp_tbl_academic_details on 
rp_tbl_personal_details.personal_id=rp_tbl_academic_details.personal_id
inner join rp_tbl_family on rp_tbl_academic_details.academic_id=rp_tbl_family.academic_id
inner join 
rp_tbl_professional on 
rp_tbl_family.family_id=rp_tbl_professional.family_id 
where rp_tbl_CreateNewJob.JobCode = @JobCode And

rp_tbl_academic_details.prof_qual = @prof_qual And
rp_tbl_academic_details.prof_stream = @prof_stream And
rp_tbl_academic_details.prof_year <= @prof_year And
rp_tbl_academic_details.grad_qual = @grad_qual And
rp_tbl_academic_details.grad_stream = @grad_stream And
rp_tbl_academic_details.grad_year <= @grad_year And

rp_tbl_family.assignment1 <> @assignment1 or
rp_tbl_family.assignment2 <> @assignment2 or
rp_tbl_family.assignment3 <> @assignment3 or
rp_tbl_family.assignment4 <> @assignment4 or
rp_tbl_family.assignment5 <> @assignment5 or

rp_tbl_professional.ex_yyyy <> @ex_yyyy And
rp_tbl_professional.emp_gross <= @emp_gross And

rp_tbl_ApplyforJob.ExpSalary <= @ExpSalary




end

IF
@@ERROR != 0 GOTO ERRORHANDLER 
COMMIT TRAN 
SELECT 1 AS retrun_val 
RETURN @retrun_val 
end
ERRORHANDLER:

BEGIN 
SELECT 0 AS retrun_val 
ROLLBACK TRAN 
RETURN @retrun_val 
END