SQL - select everything when the filter value is empty

Question

I have an SQL query in my ASP.net web application that looks like this:

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

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

My question is: why, when the text box is empty, it returns nothing? And how can I get him to return everything, as logic tells me what it should be?

Answer 1

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

Answer 2

The most suitable option that does not use 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 dynamic SQL version of SQL Server 2005+ will 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 about it in Blessing and Curse of Dynamic SQL .

Answer 3

Is it possible that @title is null or single space?

Answer 4

I'm not quite sure, but maybe this happens when @title is NULL . Operations in NULL usually return NULL , so you will compare with NULL .

Answer 5

Using

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

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

Answer 6

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