Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Null or empty full-text predicate

Tags:

t-sql

I got this error when I supplied null value to @keyword variable.And I found a solution to resolve by setting the @keyword value to '""'. The problem is when the value is set to '""' and when you run the query there is no results displayed, it must display all the records. How can I do this?

declare @keyword nvarchar(50)
set @keyword='""'

SELECT u.Id as AId FROM Users u WHERE FREETEXT((FirstName,Lastname,MiddleName),@keyword)
UNION 
SELECT c.AId FROM Certification c WHERE FREETEXT((Certification,School),@keyword)
UNION 
SELECT ad.AId FROM ApplicantDetails ad WHERE FREETEXT((City,Province,StateorRegion),@keyword)
UNION 
SELECT eb.AId FROM EducationalBackground eb WHERE FREETEXT((School,fieldofStudy),@keyword)
UNION 
SELECT ed.AId FROM EmploymentDetails ed WHERE FREETEXT((Position,DescriptionofDuties,CompanyName,City,Province,StateorRegion),@keyword)
UNION 
SELECT e.AId FROM Expertise e WHERE FREETEXT((Expertise),@keyword)
UNION 
SELECT ge.AId FROM GeographicalExperience ge WHERE FREETEXT(([Description]),@keyword)
UNION 
SELECT t.AId FROM Training t WHERE FREETEXT((Training,School),@keyword)
like image 325
user335160 Avatar asked Oct 11 '25 14:10

user335160


1 Answers

I got it....

    IF ISNULL(@keyword,'') = '' SET @keyword = '""' ;

     SELECT u.Id as AId FROM Users u WHERE @keyword = '""' 
                 OR FREETEXT((FirstName,Lastname,MiddleName),@keyword)
       UNION 
    SELECT c.AId FROM Certification c WHERE @keyword = '""' 
                 OR FREETEXT((Certification,School),@keyword)
       UNION 
    SELECT ad.AId FROM ApplicantDetails ad WHERE @keyword = '""' 
                 OR FREETEXT((City,Province,StateorRegion),@keyword)
       UNION 
    SELECT eb.AId FROM EducationalBackground eb WHERE @keyword = '""' 
                 OR FREETEXT((School,fieldofStudy),@keyword)
       UNION 
    SELECT ed.AId FROM EmploymentDetails ed WHERE @keyword = '""' 
                 OR FREETEXT                                       (Position,DescriptionofDuties,CompanyName,City,Province,StateorRegion),@keyword)
       UNION 
     SELECT e.AId FROM Expertise e WHERE @keyword = '""' 
                 OR FREETEXT((Expertise),@keyword)
       UNION 
   SELECT ge.AId FROM GeographicalExperience ge WHERE @keyword = '""' 
                 OR FREETEXT(([Description]),@keyword)
       UNION 
   SELECT t.AId FROM Training t WHERE @keyword = '""' 
                 OR FREETEXT((Training,School),@keyword)
like image 148
user335160 Avatar answered Oct 16 '25 07:10

user335160