question

crisgomez avatar image
crisgomez asked

Null or empty full-text predicate

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)
t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Blackhawk-17 avatar image
Blackhawk-17 answered
It appears that you are trying to determine the AId where the given columns are NULL or contain an empty string. Since no rows were returned I would suspect you don't have any with an empty string. As for NULL I would suggest changing your query to explicitly search for that condition. Where you have: SELECT u.Id as AId FROM Users u WHERE FREETEXT((FirstName,Lastname,MiddleName),@keyword) I would suggest: SELECT u.Id as AId FROM Users u WHERE ISNULL(FirstName) OR ISNULL(Lastname) OR ISNULL(MiddleName) ...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.