ansaurus

Question

Passing a IS NOT NULL through a sqlparameter

Answer 1

+5 A:

Assuming this is a stored procedure, say your parameter is called @Param1, have the parameter set to NULL to indicate IS NOT NULL, as follows:

SELECT ...
FROM   ...
WHERE  (
           (@Param1 IS NULL AND field1 IS NOT NULL)
           OR (field1 = @Param1)
       )

Suggested by GSerg

Testing ISNULL(@Param1, field1) = field1 with the following:

DECLARE  @test1 nvarchar(10) = 'testing',
         @test2 nvarchar(10) = NULL; -- or 'random' or 'testing'

SELECT   1
WHERE    ISNULL(@test2, @test1) = @test1;

Computations are showing as 1 for each case. This appears to be a better solution than my original answer.

Codesleuth 2010-09-27 14:23:36

That's what I said but you said it first and better. +1 to you and -1 answer to me. ;-)

Chris 2010-09-27 14:25:34

... which can be simplified to `where isnull(@param, field1) = field1`.

GSerg 2010-09-27 14:26:38

Thanks Chris :) You don't deserve a -1. No-one does (that's my philosophy on S.O.)

Codesleuth 2010-09-27 14:26:41

@GSerg: Nice simplification. I would check first on the execution plan that when `@param` is `NULL`, the computation for comparing the column is skipped (I have a feeling it isn't).

Codesleuth 2010-09-27 14:29:18

... Seems it is skipped. The Actual Execution plan only performs 1 computation in the three possible scenarios. Nice! (I'll add this to the answer)

Codesleuth 2010-09-27 14:42:07

Guys, I appreciate it. I didn't know that you can compare a column name to column name. Actually I don't quite understand it. But cool nonetheless.

dotnetN00b 2010-09-27 16:07:29

Answer 2

+1 A:

You can use the like operator:

select * from table1 where name like @param

setting @param to % if you want not null values. But then you have to escape the %.

onof 2010-09-27 14:25:12

ansaurus

tags:

views:

answers:

Passing a IS NOT NULL through a sqlparameter

related questions