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)