Select where cboValue or all if null 1

[vmon]

How do I select records using a passed parameter that may have a valid value or be null. If it is null then I want to select all records. This is what I tried without sucess.

Thanks,
vmon

SELECT * FROM tblItem WHERE tblItem.LocId = ISNULL(@cboLocId, %)

 

[ttba]

vmon,

Couple of thoughts - I assume a stored procedure... (1) Use a default value on the parameter. (2) Use an IF statement to detect the null value and fire an alternate select statement.

ttba

 

[Wyldcard9]

This is how I have an example program working, with a space between IS, and NULL.

SELECT * FROM tblItem WHERE tblItem.LocId = IS NULL(@cboLocId, %)

 

[JohnDTampaBay]

SELECT * FROM tblItem
WHERE tblItem.LocId = ISNULL(@cboLocId, tblItem.LocId)

This will not return rows where tblItem.LocId is NULL, since NULL is never equal or not equal to another value.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[aalmeida]

I take from your question that you are trying to write a stored procedure, if so then you may try this:

if (@cboLocId not null)
Begin
SELECT * FROM tblItem WHERE tblItem.LocId = @cboLocId
End
else
Begin
SELECT * FROM tblItem
end
GO

to know more from BOL:

IS [NOT] NULL
Determines whether or not a given expression is NULL.

Syntax
expression IS [ NOT ] NULL

Arguments
expression

Is any valid Microsoft® SQL Server™ expression.

NOT

Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL.

Result Types
Boolean



AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[Wyldcard9]

oooh noticed an error in mine. The equal sign needs to go away I think

SELECT * FROM tblItem WHERE tblItem.LocId IS NULL(@cboLocId, %)

 

[ESquared]

The equivalence operator IS NULL is not the same as the function IsNull(). Don't confuse them.

In my opinion, this is the best way to solve the problem. It is fast, efficient, doesn't use a function on every row like some methods do:

SELECT *
   FROM tblItem
   WHERE
      @cboLocId IS NULL
      OR tblItem.LocId = @cboLocId

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[vmon]

Very good ESquared! Prior responses questioned my ISNULL versus IS NULL. I have read up on both now. Thanks again.

vmon