x

how to integrate fulltext catalog search with FREETEXT to my query

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 
more ▼

asked Aug 01, 2010 at 11:08 PM in Default

crisgomez gravatar image

crisgomez
41 6 9 9

(comments are locked)
10|1200 characters needed characters left

0 answers: sort newest
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x279

asked: Aug 01, 2010 at 11:08 PM

Seen: 1427 times

Last Updated: Aug 01, 2010 at 11:08 PM