return all records using CONTAINS
-
I would think this would be simple but still having trouble with it.
I have a search variable passed to a CONTAINS predicate that works great until the user passes it a blank value. Ideally I want this to return everything. Using just '' generates the " Syntax error in search condition, or empty or null search condition ''. " Using * wildcard produces similar error.
For example:
DECLARE @searchValue varchar(50)
SET @searchValue = ''
SELECT firstname,lastname
FROM Customers WHERE CONTAINS(lastname,@searchValue)
What can I do to either convert the '' value to something CONTAINS will resolve to all records use some wildcard to do the same.
Dynamic SQL is not an option.
-
I have tried two possible solutions, as I am not sure if you are always using lastname in your query... (I couldn't really test this, since our database is not Full Text Indexed...).
DECLARE @searchValue varchar(50)
IF LEN( RTRIM( LTRIM( @searchValue))) > 0
BEGIN
SELECT firstname,lastname
FROM Customers
WHERE CONTAINS( lastname, @searchValue)
END
ELSE
BEGIN
SELECT firstname,lastname
FROM Customers
END
-- OR --------------------------------------------------------------
DECLARE @searchValue varchar(50)
IF LEN( RTRIM( LTRIM( @searchValue))) > 0
SET @searchValue = '%'
SELECT firstname,lastname
FROM Customers
WHERE CONTAINS( lastname, @searchValue)
I wasn't born stupid - I had to study.
-
The first solution I need to try to stay away from because the actual query is rather large and I don't want to duplicate it just for that reason.
The second solution returns the same error: Execution of a full-text operation failed. A clause of the query contained only ignored words.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply