I am geting an error when i'm selecting in sql server

See more:

SQL-Server

Hi
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,
@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