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?

Solution 1

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

Solution 2

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.

Comments

[OMG Ponies]

@John Saunders: If it's SQL Server, then you have to use RTRIM(LTRIM(@Title)) - there's no TRIM() function in TSQL :(

[John Saunders]

@Mitch: maybe LEN(RTRIM(LTRIM(@Title)))? @OMG: did a "late man's edit".

[ian93]

OK, this works when I query the database manually, but for some reason it doesn't work with the web form... I guess that's a separate question.