Hi I had created a fulltext catalog search with FREETEXT below but I dont know what is the best way on how to integrate it into the store procedure that I made. Somebody told me that I should create an Index view for Fulltext Search, how can I do this and how can I integrate this to my SP query?
--Start Fulltext Search query
SELECT u.Id FROM Users u WHERE FREETEXT((FirstName,Lastname,MiddleName),'')
UNION
SELECT c.AId FROM Certification c WHERE FREETEXT(*,'')
UNION
SELECT ad.AId FROM ApplicantDetails ad WHERE FREETEXT(*,'')
UNION
SELECT eb.AId FROM EducationalBackground eb WHERE FREETEXT(*,'')
UNION
SELECT ed.AId FROM EmploymentDetails ed WHERE FREETEXT(*,'')
UNION
SELECT e.AId FROM Expertise e WHERE FREETEXT(*,'')
UNION
SELECT ge.AId FROM GeographicalExperience ge WHERE FREETEXT(*,'')
UNION
SELECT pd.AId FROM ProjectDetails pd WHERE FREETEXT(*,'')
UNION
SELECT r.AId FROM [References] r WHERE FREETEXT(*,'')
UNION
SELECT t.AId FROM Training t WHERE FREETEXT(*,'')
--End Fulltext Search query
--Start Stored Procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Search_Applicant] (@texttoSearch nvarchar(50),@positionTitle varchar(500),@yearsofExperience int,@expectedSalary varchar(15),@currentSalary varchar(15),@positionLevel bigint,@specialization bigint,@highestqualification bigint )
AS
BEGIN
WITH LatestJob AS (SELECT * FROM
(SELECT
aId
,Position
,CompanyName
,StartDate
,Enddate
,SpecializationId
,ROW_NUMBER() OVER (PARTITION BY aId ORDER BY
(CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END) ASC
,CAST(Enddate AS datetime) DESC
) AS RN
FROM
EmploymentDetails ed
) E
WHERE RN = 1
)
,EarliestStart AS (
SELECT
aID
,SUM(DATEDIFF(YEAR, Startdate, ISNULL(Enddate,GETDATE()))) AS YearsofExperience
FROM
EmploymentDetails
GROUP BY
aId
)
, TopSkills AS (
SELECT DISTINCT
aId,
Skills = substring( ( SELECT Top 3 ', ' + Expertise
FROM Expertise e2
WHERE e2.aId = e1.aId FOR XML path(''), elements
),2,500)
FROM Expertise e1)
, Education AS (
SELECT * FROM
(SELECT
aId
,fieldofStudy
,school
,endDate
,ROW_NUMBER() OVER (PARTITION BY aId ORDER BY
(CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END) ASC
,CAST(Enddate AS datetime) DESC
) AS RN
FROM
EducationalBackground eb
) E
WHERE RN = 1
)
(
SELECT
u.Id
,u.FirstName + ' ' + u.LastName AS NAME
,ad.Age
,case when (ad.Gender=1)then 'Male' else 'Female' end as Gender
,eb.fieldofStudy
,eb.school
,eb.endDate
,lj.CompanyName as LatestEmployer
,lj.Position AS LatestPosition
,isnull(ts.Skills,'not provided') as Skills
,aps.cId
,aps.ApStatusID AS ApplicationStatus
,aps.sId AS SpecializationId
,YearsofExperience
,isnull(ad.ExpectedSalary,'not provided') ExpectedSalary
,REPLACE(REPLACE(ISNULL(ad.stateorRegion,'') + ', ' + ISNULL(c.Description, ''), ', ,', ','),',,',',') AS stateCountry
FROM
Users u
JOIN
LatestJob lj ON u.Id = lj.aid
JOIN
EarliestStart ye ON ye.aId = u.Id
JOIN
ApplicantDetails ad ON ad.aId = u.Id
JOIN
ApplicationStatus aps ON aps.aId=u.Id
JOIN
Country c ON c.Id = ad.CountryId
LEFT JOIN
TopSkills ts ON ts.aId=u.Id
LEFT JOIN
Education eb ON eb.aId=u.Id
WHERE u.RoleId = 3
AND u.UserStatusId = 1
)
UNION
(
SELECT
u.Id
,u.FirstName + ' ' + u.LastName AS NAME
,ad.Age
,case when (ad.Gender=1)then 'Male' else 'Female' end as Gender
,eb.fieldofStudy
,eb.school
,eb.endDate
,lj.CompanyName as LatestEmployer
,lj.Position AS LatestPosition
,isnull(ts.Skills,'not provided') as Skills
,''
,''
,''
,YearsofExperience
,ad.ExpectedSalary
,REPLACE(REPLACE(ISNULL(ad.stateorRegion,'') + ', ' + ISNULL(c.Description, ''), ', ,', ','),',,',',') AS stateCountry
FROM
Users u
JOIN
LatestJob lj ON u.Id = lj.aid
JOIN
EarliestStart ye ON ye.aId = u.Id
JOIN
ApplicantDetails ad ON ad.aId = u.Id
JOIN
Country c ON c.Id = ad.CountryId
LEFT JOIN
TopSkills ts ON ts.aId=u.Id
LEFT JOIN
Education eb ON eb.aId=u.Id
WHERE
u.RoleId = 3
AND u.UserStatusId = 1
AND u.ID not in(select aid
from applicationstatus)
)
END
--End Stored Procedure