CREATE PROCEDURE dbo.pr_EmployerSeekerSearch
@.CS_Parameter_Id int = null,
@.CC_Id_Nationality int = null,
@.CC_Id_Residence int = null,
@.Seeker_Owns_Car bit = null,
@.Keyword1 varchar(150) = null,
@.Keyword2 varchar(150) = null,
@.KeywordSearch char(5)= null,
@.CR_Experience_Years1 int = null,
@.CR_Experience_Years2 int = null,
@.Major_Id_1 int = null,
@.Major_Id_2 int = null,
@.Major_Id_3 int = null,
@.University_Id_1 int = null,
@.University_Id_2 int = null,
@.University_Id_3 int = null,
@.JF_Name varchar(1000) = null,
@.Language_Id_1 int = null,
@.Language_Id_2 int = null,
@.Language_Param varchar(5) = null,
@.employer_id int,
@.type varchar(20),
@.Seeker_Country int = null
AS
declare @.emp_id int
declare @.AllMajors varchar(50)select @.allMajors= Majors.Major_Name from Majors where Major_name='All' and (Major_Id=@.Major_Id_1 or Major_id=@.Major_Id_2 or Major_ID =@.Major_Id_3)
--Added new by Hussein
--The change was to check on Mjors when the @.allmajor = null else i'll return all Majors
if @.allMajors =null
begin
if (@.type='Employer')
Begin
set @.emp_id=@.employer_id
End
Else
Begin
set @.emp_id=-@.employer_id
End
if @.KeywordSearch = 'AND'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
[CR_Modification_Date] ,
Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
AND (
(
(@.keyword1 is null) or
(Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
)
AND
(
(@.keyword2 is null) or
(Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
)
)
AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
AND (
(@.JF_Name is null)
OR
(
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
OR
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
)
)
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
and(VT_ID=1 or VT_ID=2)
end
else if @.KeywordSearch = 'OR'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
[CR_Modification_Date] ,
Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
AND (
(
(@.keyword1 is not null) and (
(Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword1 + '%'))
)
OR
(
(@.keyword2 is not null) and (
(Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword2 + '%'))
)
)
AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
AND (
(@.JF_Name is null)
OR
(
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
OR
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
)
)
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
and(VT_ID=1 or VT_ID=2)
end
else
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
[CR_Modification_Date] ,
Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
AND (
(
(@.keyword1 is null) or
(Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
)
AND
(
(@.keyword2 is null) or
(Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
)
)
AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
AND (
(@.JF_Name is null)
OR
(
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
OR
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
)
)
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
and(VT_ID=1 or VT_ID=2)
end
end
else
begin
if (@.type='Employer')
Begin
set @.emp_id=@.employer_id
End
Else
Begin
set @.emp_id=-@.employer_id
End
if @.KeywordSearch = 'AND'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
[CR_Modification_Date] ,
Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
AND (
(
(@.keyword1 is null) or
(Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
)
AND
(
(@.keyword2 is null) or
(Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
)
)
AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
--AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
AND (
(@.JF_Name is null)
OR
(
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
OR
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
)
)
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
and(VT_ID=1 or VT_ID=2)
end
else if @.KeywordSearch = 'OR'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
[CR_Modification_Date] ,
Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
AND (
(
(@.keyword1 is not null) and (
(Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword1 + '%'))
)
OR
(
(@.keyword2 is not null) and (
(Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword2 + '%'))
)
)
AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
--AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
AND (
(@.JF_Name is null)
OR
(
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
OR
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
)
)
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
and(VT_ID=1 or VT_ID=2)
end
else
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
[CR_Modification_Date] ,
Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
AND (
(
(@.keyword1 is null) or
(Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
)
AND
(
(@.keyword2 is null) or
(Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
(Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
(Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
(Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
)
)
AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
--AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
AND (
(@.JF_Name is null)
OR
(
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
OR
(@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
)
)
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
and(VT_ID=1 or VT_ID=2)
end
end
GO
my problem here that if i chooesed one major_id only it will consider the other majors id as null coz i give initalize value and it will return majors which contain null values
i want it if it found majorid2 for example null ignore it just search for majors which have specific id not null hope anybody can helpHello can anybody help|||I haven't replied because that is just too much code to look through and I am not clear on what/where your problem is.
Could you pare down the code to just a relevant chunk and re-explain your problem?
Terri|||i don't know where the problem in the code
but my problem here that i the user may have 3 majors
if he chooe one the otheres will be null but i don't want my query to return null value
for example
the user enter
major_id_1 = 183
then the others will be null
i want to recieve the result with just major_id 183 i don't want any null value hope u get this but where the error in the code i don't know|||You are saying "my query" but you have 7 long queries below with embedded logic. That is too much to expect someone to look at, understand, and debug.
Please pare your code down to 1 or 2 queries that are not functioning as you you desire and then we should be able to help. You need to strip out all of the code not related to the problem -- and actually in doing so you might discover the solution for yourself.
Terri
No comments:
Post a Comment