December 6, 2013 at 2:47 am
How do I optionally include a Contains search in my query, based on the input? In the below situation, I don't want to search if the criteria is empty.
In non-FTS I would do the following:
DECLARE @Criteria1 varchar(10) = 'find',
@Criteria2 varchar(10) = ''
SELECT *
FROM TableX
WHERE (@Criteria1='' OR Column1=@Criteria1)
AND (@Criteria2='' OR Column2=@Criteria2) --Correctly returns True, if no criteria supplied
How to implement this using Contains? I tried different approaches, like:
SELECT *
FROM TableX
WHERE (CONTAINS(Column1, @Criteria1))
AND (CONTAINS(Column2, @Criteria12)) --Error regarding no search word
SELECT *
FROM TableX
WHERE (CONTAINS(Column1, @Criteria1))
AND (CONTAINS(Column2, @Criteria12)) --Returns False, excluding the whole record (unwantedly)
SELECT *
FROM TableX
WHERE (@Criteria1='' OR CONTAINS(Column1, @Criteria1))
AND (@Criteria2='' OR CONTAINS(Column2, @Criteria12)) --Runs very slow and ineficiently
Do I have to create a separate query for echt combination of parameters, or is there a certain writing in the Contains statement? In other words, how do I get Contains return True immediately, when the criteria is empty?
December 10, 2013 at 12:34 am
Anybody?
I now have the following, which performs better, but doesn't seem optimal to me:
DECLARE @AllIDs table (ID int PRIMARY KEY)
IF @Criteria1<>'' AND @Criteria2=''
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column1, @Criteria1)
IF @Criteria1='' AND @Criteria2<>''
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column2, @Criteria2)
IF @Criteria1<>'' AND @Criteria2<>''
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column1, @Criteria1)
INTERSECT
SELECT ID FROM TableX WHERE CONTAINS(Column2, @Criteria2)
SELECT *
FROM TableX
WHERE ID IN (SELECT ID FROM @AllIDs)
The downsides of this methodology are:
- 2 searches in TableX: one for filling @AllIDs, and another one for actually retrieving the data
- If there are more optional search criteria, the IF statements will explode (although there are other writings to apply then)
Although the above works, I cannot imagine that there is a better way, built into Full Text Search functions.
Help is appreciated!
December 10, 2013 at 6:33 am
This is one of those 'it depends'
Doing two separate queries is not necessarily worse than one, I have in the past done two and found no problem with performance, in fact sometimes it is better.
If you are worried about doing more than one query then join the criteria together before the query, eg
IF @Criteria1<>'' AND @Criteria2<>''
SET @Criteria1 = @Criteria1 + ' AND ' + @Criteria2
ELSE
SET @Criteria1 = @Criteria1 + @Criteria2
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column1, @Criteria1)
p.s. The only way to find the optimum solution is to try different methods and see which has the best performance / plan that meets your needs.
Far away is close at hand in the images of elsewhere.
Anon.
December 10, 2013 at 7:01 am
Hi David,
Thanks for your reply.
Your solution won't work, I'm affraid, because @Criteria1 applies to Column1, and @Criteria2 applies to Column2. When one of the @Criteria parameters is empty, it should be ignored.
Regards, Juul
December 10, 2013 at 7:12 am
wouldn't switching to a union work, i think? i think the AND would short circuit any table scan right away, and will perform a bit better.
SELECT
*
FROM TableX
WHERE @Criteria1 <> ''
AND CONTAINS(Column1, @Criteria1))
UNION
SELECT
*
FROM TableX
WHERE @Criteria2 <> ''
AND CONTAINS(Column2, @Criteria2))
Lowell
December 10, 2013 at 7:45 am
Lowell (12/10/2013)
wouldn't switching to a union work, i think? i think the AND would short circuit any table scan right away, and will perform a bit better.
SELECT
*
FROM TableX
WHERE @Criteria1 <> ''
AND CONTAINS(Column1, @Criteria1))
UNION
SELECT
*
FROM TableX
WHERE @Criteria2 <> ''
AND CONTAINS(Column2, @Criteria2))
That will still produce the message as the CONTAINS will still be evaluated even if criteria is blank
Far away is close at hand in the images of elsewhere.
Anon.
December 10, 2013 at 7:46 am
Juul van Iersel (12/10/2013)
Hi David,Thanks for your reply.
Your solution won't work, I'm affraid, because @Criteria1 applies to Column1, and @Criteria2 applies to Column2. When one of the @Criteria parameters is empty, it should be ignored.
Regards, Juul
Ooops! Sorry missed that.
try this
IF @Criteria1='' SET @Criteria1 = '*'
IF @Criteria2='' SET @Criteria2 = '*'
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX
WHERE CONTAINS(Column1, @Criteria1)
OR CONTAINS(Column2, @Criteria2)
This will not select any rows if criteria is '*' but using or at least one will select something.
Far away is close at hand in the images of elsewhere.
Anon.
December 10, 2013 at 8:02 am
Hi Lowell,
i think the AND would short circuit any table scan right away, and will perform a bit better.
That indeed did the trick, I was sort of looking for, thanks a lot!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply