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)
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With