Setting Parameter = IS NULL

  • Thread starter Thread starter James Hancock
  • Start date Start date
J

James Hancock

I've got a Command that has a parameter. Sometimes it will be a
uniqueidentifier (Guid) but there is also the case of looking for the column
being null.

How do I tell the Parameter that I'm looking for NULLs? If I set .Value =
null I get an error, if I set it to DBNull.value I don't get a valid result.
If I set it to SQLType.SQLGuid.Null I don't get a valid result.

Thanks!
James Hancock
 
James Hancock said:
I've got a Command that has a parameter. Sometimes it will be a
uniqueidentifier (Guid) but there is also the case of looking for the column
being null.

How do I tell the Parameter that I'm looking for NULLs? If I set .Value =
null I get an error, if I set it to DBNull.value I don't get a valid result.
If I set it to SQLType.SQLGuid.Null I don't get a valid result.
Click to expand...

Setting it to DBNUll.Value should do the right thing. I suggest you do
that and use a profiler on the database to see what's being sent.
 
I did that and hte problem is that it's generating a query with = NULL
instead of IS NULL.

IS NULL pulls the values pefectly. = NULL does not (SQL 2000).

Anyone have any suggestion on how to make this work?

Thanks,
James Hancock
 
James Hancock said:
I did that and hte problem is that it's generating a query with = NULL
instead of IS NULL.

IS NULL pulls the values pefectly. = NULL does not (SQL 2000).
Click to expand...

Ah yes, the old NULL != NULL chestnut...
Anyone have any suggestion on how to make this work?
Click to expand...

Hmm... the only thing I can think of would be some horrible contortion
where you use the parameter twice. Something like:

SELECT * FROM FOO WHERE
(@Foo IS NULL AND X IS NULL) OR (X=@Foo)

I haven't tested the above, but it *might* work!
 
Back
Top