LIKE NULL + '%' ?

_{Thursday, September 6, 2007 6:39 PM}

I'm trying to write a procedure with three search parameter inputs.  I'm trying to allow NULL selections in the search criteria to represent any value for the corresponding column.  I tried to do that by using 

Code Snippet

WHERE [COLNAME] LIKE @param + '%'

hoping that @param = NULL would result in

Code Snippet

WHERE [COLNAME] LIKE '%'

Am I way off base here?  Or just a little?  Here's what I tried...

Code Snippet

CREATE PROCEDURE myprocedure

@step nvarchar(15),

@tool nvarchar(15),

@keyword nvarchar(48)

AS

BEGIN

SET NOCOUNT ON;

SELECT [ENTERED] DateEntered, [STEP] Step, [TOOL] Tool, [FILENAME] InformNote, [PATH] + [FILENAME] Path

FROM MYTABLE

WHERE [STEP] LIKE @step + '%'

AND [TOOL] LIKE @tool + '%'

AND [FILENAME] LIKE '%' + @keyword + '%'

END

Thanks for your help.

_{Thursday, September 6, 2007 6:56 PM ✅Answered}

One way is to change the NULL value to an empty string character:

SELECT [ENTERED] DateEntered, [STEP] Step, [TOOL] Tool, [FILENAME] InformNote, [PATH] + [FILENAME] Path

FROM MYTABLE

WHERE [STEP] LIKE coalesce(@step**,'')** + '%'

AND [TOOL] LIKE coalesce(@tool**,'')** + '%'

AND [FILENAME] LIKE '%' + coalesce(@keyword**,'')** + '%'

That will give you what you want, it might not be the perfect performer.  Alternatively, you could try:

SELECT [ENTERED] DateEntered, [STEP] Step, [TOOL] Tool, [FILENAME] InformNote, [PATH] + [FILENAME] Path

FROM MYTABLE

WHERE ([STEP] LIKE @step + '%'  or @step is null)

AND ([TOOL] LIKE @tool + '%' or @tool is null)

AND ([FILENAME] LIKE '%' + @keyword + '%' or @keyword is null)

END

_{Thursday, September 6, 2007 6:50 PM}

'Hunchback' answered a question similar to this as...

Try:

case

when (@Param is null) or (table.fieldname is null) then 1 

else case when exists(select * from dbo.fn_split(@Param, ',') as t where t.value = table.fieldname) then 1 else 0 end

end = 1

Not sure it will give good performance.

Here is a very good article about this theme.

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

_{Thursday, September 6, 2007 6:52 PM}

You could check the parameter before you execute the query like this

Code Snippet

IF @param IS NULL

SET @param = '%'

WesleyB

Visit my SQL Server weblog @ http://dis4ea.blogspot.com

_{Thursday, September 6, 2007 8:34 PM}

Try:

...

WHERE

   [STEP] LIKE coalesce(@step + '%', '%')

   AND [TOOL] LIKE coalesce(@tool + '%', '%')

   AND [FILENAME] LIKE coalesce('%' + @keyword + '%', '%')

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB

_{Thursday, September 6, 2007 10:16 PM}

This seems to work really well...

...except I forgot to mention that the [STEP] and [TOOL] fields can contain NULL values!

Man, I'm just wonderful at explaining my problems...

_{Friday, September 7, 2007 12:38 AM}

Code Snippet

where IsNull([Step], IsNull(@step,'')) LIKE  IsNull(@step,'') + '%'

and IsNull([Tool], IsNull(@tool,'')) LIKE  IsNull(@Tool,'') + '%'

where IsNull([FileName], IsNull(@keyword,'')) LIKE  IsNull(@keyword,'') + '%'

_{Friday, September 7, 2007 2:51 PM}

You guys are awesome.  These suggestions were all very helpful. 

I ended up going with Rusag's, b/c it does all I need in the WHERE, and doesn't require any additional lines for parameter reassignment. 

Thanks very much to everyone.

_{Friday, September 7, 2007 7:19 PM}

OK so now that I have more entries in my table I've found a problem...

If I enter a non-null value into @tool, it returns all the values where [TOOL] = @tool and all the values where [TOOL] is NULL.

I need to find a way to treat NULL input parameters as wild cards and non-null input parameters as required values for returned items...

Any more suggestions?  Thanks.

--EDIT--

Louis' second solution works for this problem.  Thanks Louis!

_{Wednesday, September 12, 2007 4:26 AM}

NULL + 42 gives NULL

You can't compare with null, unless you use "... is null"

Be also aware of:

select @a = null;

if @a = 1

begin

  select 1;

end;

else

begin

  select 'not 1';

end;

if @a <> 1

begin

  select 'not 1';

end;

else

begin

  select '1?';

end;

in both cases you will end in else-part when @a is null...

_{Wednesday, September 12, 2007 7:59 PM}

Code Snippet

declare @colNameVar nvarchar(15)

if @param is null

set @colNameVar = ''

else

set @colNameVar = @param + '%'

 

...

where

(ColName like @colNameVar or @colNameVar = '')

This code will return only all values or the specified value, not both, and not any NULL values unless all values were selected.

You don't want NULL parameters appearing in your WHERE clause as SQL Server sometimes has a hard time generating a cachable query plan and the performance will therefore be horrible.