sql server 2008 - How to do write this Join Statement? -

in sql i'm select data more 1 table using join. want select multiple condition , therefor using , , or. here need if jobcode is=1 select data jobcode 1, if jobcode 2 show data jobcode 2.but error when puting jobcode=1 swoing data, 2 same data, 0 same data. i'm making mistake. 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, @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)=''  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 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 ''any day'' when 2 ''week day'' when 3 ''weekends'' else ''other'' end 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 1=1 , rp_tbl_createnewjob.jobcode =' + convert (varchar(50),@jobcode)+  '  ,  rp_tbl_personal_details.dob between ' + ''''+ convert(varchar(50),@dob) + ''''+'  ,  ' +''''+ convert(varchar(50),@do)+'''' if (@prof_qual!='') begin set @proce =@proce + ' , rp_tbl_academic_details.prof_qual in ('+'''' + convert (varchar (50),@prof_qual)+''')' end if (@prof_stream!='0') begin set @proce =@proce + ' , rp_tbl_academic_details.prof_stream in (' + @prof_stream +')' end set @proce =@proce + ' , rp_tbl_academic_details.prof_year between ' + ''''+ convert(varchar(50),@prof_year1 ) + '''' + '  , ' + '''' + convert(varchar(50),@prof_year )+ ''''   if (@grad_qual!='') begin set @proce =@proce + ' , rp_tbl_academic_details.grad_qual in (''' + @grad_qual +''')' end if (@grad_stream!='0') begin set @proce =@proce + ' , 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 ) + '''' + '  , ' + '''' + 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 , @e_yyyy!=100) begin set @proce =@proce + ' , rp_tbl_professional.ex_yyyy between ' + ''''+ convert(varchar(50),@ex_yyyy ) + '''' + '  , ' + '''' + convert(varchar(50),@e_yyyy )+ ''''   end if (@emp_gross !=0 , @em_gross!=50000000) begin set @proce =@proce + ' or rp_tbl_professional.emp_gross between ' + ''''+ convert(varchar(50),@emp_gross ) + '''' + '  , ' + '''' + convert(varchar(50),@em_gross )+ ''''   end if (@expsalary !=0 , @epsalary !=50000000) begin set @proce =@proce + ' or rp_tbl_applyforjob.expsalary between ' + ''''+ convert(varchar(50),@expsalary ) + '''' + '  , ' + '''' + convert(varchar(50),@epsalary )+ ''''   end print (@proce) exec(@proce) end end 

i test code getting output query , got. omitted other statements statement , made alias table.

where 1=1     , cnj.jobcode =0     , pd.dob between 'jan  1 1900 12:00am' , 'jan  1 2050 12:00am'      , ad.prof_qual in ('')      , ad.prof_stream in (0)      , ad.prof_year between '0   '  , '2050'      , ad.grad_qual in ('bsc')      , ad.grad_stream in (0)      or ad.grad_year between '0   '  , '2050'      or fa.assignment1 = 0      or fa.assignment2 = 0      or fa.assignment3 = 0      or fa.assignment4 = 0      or fa.assignment5 = 0      , pr.ex_yyyy between '0'  , '100'      or pr.emp_gross between '0'  , '50000000'      or apr.expsalary between '0'  , '50000000' 

from obsetvation, i'm not sure why include '1=1'. becomes true. , reason why getting all/same data because of or statements. might need group or statements inside parenthesis.

and (         ad.grad_year between '0   '  , '2050'      or fa.assignment1 = 0      or fa.assignment2 = 0      or fa.assignment3 = 0      or fa.assignment4 = 0      or fa.assignment5 = 0     )     , pr.ex_yyyy between '0'  , '100'      , (        pr.emp_gross between '0'  , '50000000'      or apr.expsalary between '0'  , '50000000') 

also noticed spaces on output of variables @ betweeen statement, might need deal it.