NULL AND NOT NULL in SQL WHERE 1

[davism]

Hi all,

Is there a way to do something like:

DECLARE @TestID varchar(10),
@LastName varchar(70),
@FirstName varchar(30),
@MiddleName varchar(25),
@BirthDate datetime,
@SSN varchar(9),
@Address varchar(100),
@State char(2),
@Zip varchar(6)


SELECT @TestID=pr.TestID
FROM
TestA..Test r
INNER JOIN TestA..Sub pr
ON r.TempID = pr.TempID
LEFT JOIN TestA..Sub2 pa
on pa.TempID = pr.TempID
LEFT JOIN TestA..Sub3 pl
on pl.TempID = pr.TempID
LEFT JOIN TestA..Sub4 pd
on pd.TempID = pr.TempID
INNER JOIN TestA..FileA rf
ON r.FileAFileID = rf.FileAFileID
WHERE
r.FileAFileID = 12934 AND
@TsetID > '' AND
/*@LastName = pr.LastName, AND
@FirstName = pr.FirstName AND
@MiddleName = pr.MiddleName AND
@BirthDate = pr.BirthDate AND
@SSN = pr.SSN AND
@Address = pa.Address AND
@State = pa.State AND
@Zip = pa.PostalCode*/

Now, is there a way to have like the @LastName,@FirstName, @Middlename, etc happen IF the value contained isn't NULL and if it's not NULL then do the equals "=" like it is doing? It would be sorta like a CASE statement in a WHERE.

Anybody have any information on how something like this can be done?

Any information would be greatly appreciated.

Thanks

 

[ousoonerjoe]

You mean something along these lines?

WHERE ((@LastName = pr.LastName) OR (@LastName IS NULL))

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[davism]

Wouldn't:

ISNULL(@LastName, pr.LastName) = pr.LastName

Be in essence the same?

 

[ousoonerjoe]

What exactly are you looking to return if @LastName IS NULL?

ISNULL(@LastName, pr.LastName) = pr.LastName
--Would be a singular result.
WHERE ((@LastName = pr.LastName) OR (@LastName IS NULL)) 
--Would return multiple possibilities.

Coffee isn't in full gear yet, but if you're looking to effectively replace the NULL with a given value, then ISNULL is the answer. If you're looking for a recordset return of options, then the WHERE maybe more of what you need.

Try it. See what works best for your need.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gmmastros]

Wouldn't:

ISNULL(@LastName, pr.LastName) = pr.LastName

Be in essence the same?

The answer is: It depends.

If @LastName is not null, then the left side of the expression reduces to:
[tt]@LastName = pr.LastName[/tt]

If @LastName is NULL, then the left side of the expression reduces to:
[tt]pr.LastName = pr.LastName[/tt]

At first glance, this would appear to do exactly what you want. And in some circumstances, it does.

Stepping back for a moment. If @LastName is null, that means we want to disregard the filter on the last name. So... LastName = LastName should not filter out any rows, EXCEPT... if LastName allows NULL, then this row would be filtered out also.

Ex:

Declare @Test Table(LastName VarChar(20))

Insert Into @Test Values('Washington')
Insert Into @Test Values('Lincoln')
Insert Into @Test Values('')
Insert Into @Test Values(NULL)

Declare @LastName VarChar(20)

Set @LastName = NULL

Select *
From   @Test
Where  ISNULL(@LastName, LastName) = LastName

Select *
From   @Test
Where  @LastName Is NULL OR LastName = @LastName

The first query, with the ISNULL, returns 3 rows and the second query returns 4. If your LastName column allows NULL, do not use the IsNULL query.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Thanks for the information. I ended up using you're initial statement with the OR. It works appropriately. I guess I was just looking too deep into this.

Thanks!