SQL - Select all when filter value is empty

Question

I have a SQL query in my ASP.net web app that looks like this:

SELECT * FROM [Records] WHERE ([title] LIKE '%' + @title + '%')

@title, of course, is the value of a text box on the page.

My question is, why, when the text box is empty, does this return nothing? And how can I make it return everything, like logic tells me it ought to?

Answer 1

Is it possible the value of @title is holding null or a single space?

Answer 2

I'm not quite sure, but maybe this happens when @title is NULL. Operations on NULL generally return NULL, so you'd be comparing to NULL.

Answer 3

SELECT * FROM [Records] 
WHERE @title IS NULL OR LEN(@Title) = 0 OR ([title] LIKE '%' + @title + '%') 

Answer 4

Use

SELECT * FROM [Records] WHERE (isnull(@title,'') = '' or [title] LIKE '%' + @title + '%') 

Or do a check for an empty title in the client code, pass NULL if so, and do a condition: "@title is null or...".

Answer 5

NULL plus anything is NULL so while you might expect to get '%%' you are actually getting NULL. Since nothing is LIKE NULL (things IS NULL instead) you get nothing. You might try something like WHERE [TITLE] LIKE ISNULL('%' + @title + '%', '%'); If the first argument to ISNULL is NULL, it returns the second arg.

Answer 6

The most sargable option, short of using dynamic SQL, is to use an IF statement and two queries:

IF LEN(@Title) > 0

  SELECT r.*
    FROM RECORDS r
   WHERE r.title LIKE '%'+ @Title +'%'

ELSE

  SELECT r.*
    FROM RECORDS r

The SQL Server 2005+ dynamic SQL version would resemble:

DECLARE @SQL NVARCHAR(4000)
   SET @SQL = 'SELECT r.*
                 FROM RECORDS r 
                WHERE 1 = 1' --will be optimized out, easier to add clauses

   SET @SQL = @SQL + CASE LEN(@Title)
                       WHEN 0 THEN ''
                       ELSE ' AND r.name LIKE ''%''+ @Title +''%'' '   
                     END

BEGIN

  EXEC sp_executesql @SQL, N'@Title VARCHAR(#)', @Title

END

Unlike EXEC, sp_executesql will cache the query plan. You can read more about it in the Blessing & Curse of Dynamic SQL.