Search code examples
sql-servert-sqlfreetext

Select every row with contains

I have a function that returns a table based on some filter. One of the filters is a title which can be null The following query is possible when filtering the table:

DECLARE @DocumentTitle VARCHAR(150)
SET @DocumentTitle = NULL
SELECT * 
FROM [table]
    WHERE   
    FREETEXT([Title], @DocumentTitle)

This means that the user doesn't want to filter the title. So I should set the @DocumentTitle a value that returns everything.

What is that value?

(I've already tried with '', '""', '""', ' ', '').

OR

How I can make this work?

(@DocumentTitle IS NOT NULL AND FREETEXT([Title], @DocumentTitle))

because @DocumentTitle is null but it goes further to freetext.

Thank you!

Solution

  • SOLUTION: 

    declare    @Search nvarchar(1000) = NULL --'test'
set    @Search = ISNULL(@Search , '""')

--if @Search is null returns everything
select    *
from    TestTabel
where    contains(Column, @Search ) OR @Search = '""'