Nullif and Case Expression

[n36z]

If @value was not null, How would you express it with a NOT Isnull expression in a case statement?


declare @value1 int

set @value1=1

select *
from table1
where 0 = 0

AND
CASE
WHEN "NOT nullif(@value1,'')"
THEN columnA
END = @value1

 

[gmmastros]

It appears as though you are trying to write a "search" type of query, and having a hard time with it. Let me explain how this usually works...

Suppose you had a table of people that included name, eyeColor and shoeSize. Now suppose you wanted to write a stored procedure that allowed you to search on any of those columns. It would look something like this:

Create Procedure SearchPeople
  @Name VarChar(100) = NULL,
  @EyeColor VarChar(20) = NULL,
  @ShowSize Numeric(3,1) = NULL
As
SET NOCOUNT ON

Select *
From   People
Where  (@Name Is NULL OR Name = @Name)
       And (@EyeColor Is NULL OR EyeColor = @EyeColor)
       And (@ShoeSize Is NULL OR ShoeSize = @ShoeSize)

This allows you to pass any combination of name, EyeColor, and ShoeSize in to the procedure and have it return just the data you are looking for.

So.... applying this to your query, I suggest you try:

declare @value1 int

set @value1=1

select *
from   table1
where  0 = 0
       AND (@Value1 Is NULL Or ColumnA = @Value1)

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

 

[n36z]

This works, thanks.

 

[n36z]

Can this still be done with a case statement and NullIf?

 

[Qik3Coder]

The syntax for what you are after is:


and a.col1 = CASE WHEN @Column1 IS NOT NULL THEN @Column1 ELSE a.col1 END


George's syntax is a little clearer.

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[gmmastros]

Actually, Qik3Coder, that code may return different results than the code I suggested. Specifically, if there is a NULL in the data, your query wouldn't return it, but mine would.

The following code demonstrates this:

Declare @People Table(Name VarChar(20), EyeColor VarChar(20))

Insert Into @People Values('George','Brown')
Insert Into @People Values('Qik3Coder',NULL)

Declare @EyeColor VarChar(20)
Set @EyeColor = NULL

Select *
From   @People
Where  (@EyeColor Is NULL Or EyeColor = @EyeColor)

Select * 
From   @People
Where  EyeColor = Case When @EyeColor Is Not NULL Then @EyeColor Else EyeColor End

Of course, if the column you are using does not allow nulls, then it would work just fine.


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

 

[n36z]

Thank You