How to do write this Join Statement?

In Sql I'm Select data from more than one table using join. I want where Select with multiple condition and therefor i am using and , or. Here I need If jobCode is=1 then select all the data where jobcode is 1, if JobCode is 2 the show all the data where jobcode is 2.But error is when i am puting JobCode=1 then its swoing all the data, 2 same data, 0 same data. Where I'm making mistake. Help me.

USE [Recruitment]
GO
/****** Object:  StoredProcedure [dbo].[sp_AdvanceSearch]    Script Date: 05/11/2011 09:47:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_AdvanceSearch]

@callval int=9,
@JobCode int=0,
@location_id int=0,
@Position varchar(20)='',
@flag char(1)='',
@JobDesc varchar(MAX)='',
@PostStreamId varchar(10)='',
@GraduQuali varchar(100)='',
@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,
@academic_id int=0,
@family_id int=0,
@profess_id int=0,
@applyId int=0,
@dtInterview int=0,
@TimeReq int=0,开发者_JS百科
@ExpSalary decimal(12,2)=0,
@EpSalary decimal(12,2)=50000000,
@Resume varchar(max)='',
@howdid int=0,
@dob datetime='1/1/1900',
@do datetime='1/1/2050',
@applydate datetime='',
@prof_qual varchar(10)='',
@prof_stream varchar(50)='0',
@prof_stream1 varchar(24)='',
@prof_stream2 varchar(24)='',
@prof_stream3 varchar(24)='',
@prof_year char(4)='2050',
@grad_qual varchar(10)='Bsc',
@grad_stream varchar(15)='0',
@grad_stream1  varchar(15)='',
@grad_stream2  varchar(15)='',
@grad_stream3  varchar(15)='',
@grad_year char(4)='2050',
@prof_year1  char(4)='0',
@grad_year1 char(4)='0',
@assignment1 int=0,
@assignment2 int=0,
@assignment3 int=0,
@assignment4 int=0,
@assignment5 int=0,
@ass1 int=6,
@ass2 int=6,
@ass3 int=6,
@ass4 int=6,
@ass5 int=6,
@ex_yyyy int=0,
@e_yyyy int=100,
@em_gross decimal(12,2)=50000000,
@emp_gross decimal(12,2)=0,
@city_id int=0,
@city_name varchar(15)=''

as
begin

if @callval=9
begin
declare @proce varchar(5000)

set @proce ='select rp_tbl_personal_details.c_f_name  +''''+c_m_name +''''+ c_l_name as name,
rp_tbl_personal_details.dob,rp_tbl_personal_details.email,
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.personal_id,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.personal_id=rp_tbl_personal_details.personal_id
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 1=1 and rp_tbl_CreateNewJob.JobCode =' + convert (varchar(50),@JobCode)+  '  and  rp_tbl_personal_details.dob BETWEEN ' + ''''+ convert(varchar(50),@dob) + ''''+'  and  ' +''''+ convert(varchar(50),@do)+''''
if (@prof_qual!='')
begin
set @proce =@proce + ' and rp_tbl_academic_details.prof_qual in ('+'''' + convert (varchar (50),@prof_qual)+''')'
end
if (@prof_stream!='0')
begin
set @proce =@proce + ' and rp_tbl_academic_details.prof_stream in (' + @prof_stream +')'
end
set @proce =@proce + ' and rp_tbl_academic_details.prof_year BETWEEN ' + ''''+ convert(varchar(50),@prof_year1 ) + '''' + '  And ' + '''' + convert(varchar(50),@prof_year )+ ''''  
if (@grad_qual!='')
begin
set @proce =@proce + ' and rp_tbl_academic_details.grad_qual in (''' + @grad_qual +''')'
end
if (@grad_stream!='0')
begin
set @proce =@proce + ' and rp_tbl_academic_details.grad_stream in (' + @grad_stream +')'
end
set @proce =@proce + ' or rp_tbl_academic_details.grad_year BETWEEN ' + ''''+ convert(varchar(50),@grad_year1 ) + '''' + '  And ' + '''' + convert(varchar(50),@grad_year )+ ''''  
if (@assignment1 != 0)
begin
set @proce =@proce + ' or rp_tbl_family.assignment1 = ' + convert(varchar(50),@assignment1 )
end
if (@assignment2 != 0)
begin
set @proce =@proce + ' or rp_tbl_family.assignment2 = ' + convert(varchar(50),@assignment2 )
end
if (@assignment3 != 0)
begin
set @proce =@proce + ' or rp_tbl_family.assignment3 = ' + convert(varchar(50),@assignment3 )
end
if (@assignment4 != 0)
begin
set @proce =@proce + ' or rp_tbl_family.assignment4 = ' + convert(varchar(50),@assignment4 )
end
if (@assignment5 != 0)
begin
set @proce =@proce + ' or rp_tbl_family.assignment5 = ' + convert(varchar(50),@assignment5 )
end
if (@ex_yyyy !=0 and @e_yyyy!=100)
begin
set @proce =@proce + ' and rp_tbl_professional.ex_yyyy BETWEEN ' + ''''+ convert(varchar(50),@ex_yyyy ) + '''' + '  And ' + '''' + convert(varchar(50),@e_yyyy )+ ''''  
end
if (@emp_gross !=0 and @em_gross!=50000000)
begin
set @proce =@proce + ' or rp_tbl_professional.emp_gross BETWEEN ' + ''''+ convert(varchar(50),@emp_gross ) + '''' + '  And ' + '''' + convert(varchar(50),@em_gross )+ ''''  
end
if (@ExpSalary !=0 and @EpSalary !=50000000)
begin
set @proce =@proce + ' or rp_tbl_ApplyforJob.ExpSalary BETWEEN ' + ''''+ convert(varchar(50),@ExpSalary ) + '''' + '  And ' + '''' + convert(varchar(50),@EpSalary )+ ''''  
end
print (@proce)
exec(@proce)
end
end

I actually test your code by getting the output query and this is what i got. I omitted the other statements up to the where statement and made an alias to your table.

WHERE 1=1
    AND cnj.JobCode =0
    AND pd.dob BETWEEN 'Jan  1 1900 12:00AM' AND 'Jan  1 2050 12:00AM' 
    AND ad.prof_qual IN ('') 
    AND ad.prof_stream IN (0) 
    AND ad.prof_year BETWEEN '0   '  AND '2050' 
    AND ad.grad_qual IN ('Bsc') 
    AND ad.grad_stream IN (0) 
    OR ad.grad_year BETWEEN '0   '  AND '2050' 
    OR fa.assignment1 = 0 
    OR fa.assignment2 = 0 
    OR fa.assignment3 = 0 
    OR fa.assignment4 = 0 
    OR fa.assignment5 = 0 
    AND pr.ex_yyyy BETWEEN '0'  AND '100' 
    OR pr.emp_gross BETWEEN '0'  AND '50000000' 
    OR apr.ExpSalary BETWEEN '0'  AND '50000000'

From my obsetvation, i'm not really sure why you include '1=1'. This would always becomes true. And the reason why you are getting all/same data is probably because of the OR statements. You might need to group your OR statements inside a parenthesis.

AND ( 
       ad.grad_year BETWEEN '0   '  AND '2050' 
    OR fa.assignment1 = 0 
    OR fa.assignment2 = 0 
    OR fa.assignment3 = 0 
    OR fa.assignment4 = 0 
    OR fa.assignment5 = 0
    )
    AND pr.ex_yyyy BETWEEN '0'  AND '100' 
    AND (
       pr.emp_gross BETWEEN '0'  AND '50000000' 
    OR apr.ExpSalary BETWEEN '0'  AND '50000000')

Also be just noticed the spaces on the output of your variables at the BETWEEEN statement, you might need to also deal with it.