与和 [英] And and OR

问题描述

嗨
我想搜索多列基础的候选人详细信息.我尝试如下所示的代码.如果任何条件为真，我就需要显示数据.

Hi
I want to search candidate details on basic of multiple column.I''m try like that see below code. I need if any condition is true then data should show.

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

推荐答案

我认为问题在于将条件与AND和OR结合在一起.

因此，假设您希望通过以下条件

1)在column1，column2，column3中，任何一个都应等于"ABC"
AND
2)在column4，column5中，两列均应等于"XYZ"

然后您可以指定WHERE条件，例如

I think the problem is on combining the condition with AND and OR.

So Suppose if you want following condition to be passed

1) among column1,column2,column3 any of one should be equal to "ABC"
AND
2) among column4,column5 both the column should be equal to "XYZ"

Then you could specify WHERE condition like

where
(column1 == "ABC" OR column2="ABC" OR Column3="ABC") AND (Column4="XYZ" AND Column5="XYZ")

希望我对您提出的问题感到满意.

I hope I''ve got you well with your concern on asking question.

这篇关于与和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！