T-SQL with case statement 1

[peac3]

Hi Guys,

I have simple query within stored proc which I'm trying to nail it, this code below is working

DECLARE @Code nvarchar
SET @Code = '2D'

Select SYSTSClientID = c.SYST_CLNT_I,  
       Code      = c._C,  
       ClientName    = c.CLNT_M  
From   CLNT            c   (nolock),  
       CLNT_ULT_RISK   cur (nolock)  
Where  c.SYST_STUS_C   = ' '  
AND    c.SYST_CLNT_I   = cur.SYST_CLNT_I  
AND c._C like @Code + '%'

but when I try to put condition as below it doesn't work

DECLARE @Code nvarchar
SET @Code = '2D'

Select SYSTSClientID = c.SYST_CLNT_I,  
       Code      = c._C,  
       ClientName    = c.CLNT_M  
From   CLNT            c   (nolock),  
       CLNT_ULT_RISK   cur (nolock)  
Where  c.SYST_STUS_C   = ' '  
AND    c.SYST_CLNT_I   = cur.SYST_CLNT_I     
AND 
CASE 
	WHEN @Code IS NOT NULL THEN c._C like @Code + '%' 
	ELSE @ClientName IS NOT NULL THEN c.CLNT_M like '%' + @ClientName + '%'  END	
Order By c._C

I wonder where I did wrong?

Cheers

 

[fredericofonseca]

try

Where  c.SYST_STUS_C   = ' '  
AND    c.SYST_CLNT_I   = cur.SYST_CLNT_I     
AND CASE 
    WHEN @Code IS NOT NULL THEN c._C
    ELSE @ClientName IS NOT NULL THEN c.CLNT_M 
    END	

    LIKE

    CASE 
    WHEN @Code IS NOT NULL THEN  @Code + '%' 
    ELSE @ClientName IS NOT NULL THEN '%' + @ClientName + '%'
    END

or (which looks clearer to me)

Where  c.SYST_STUS_C   = ' '  
AND    c.SYST_CLNT_I   = cur.SYST_CLNT_I     
AND (   (@Code IS NOT NULL and c._C like  @Code + '%')
     or (@ClientName IS NOT NULL and c.CLNT_M  like '%' + @ClientName + '%')
    )

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[TamarGranor]

Seems to me the problem is trying to put a test expression in the ELSE clause of CASE. ELSE is for the last one, which takes effect is none of the WHENs are matched.

Tamar

 

[gmmastros]

Actually, this is just a simple syntax problem.

Simplified:

[tt]
CASE WHEN (Condition Evaluation 1) THEN (Check Condition 1)
ELSE (Condition Evaluation 2) THEN (Check Condition 2)
END
[/tt]

This does not work because the syntax is wrong.

The problem is... you cannot have a "check condition" in the THEN part of a case when. I find it helpful to think of Case/When as operating on a single value and returning a single value.

For example, run this:

Select 1
Where  Case When 1=1 Then 2=2 End

This is the basic structure of the original code that does not work. This code also does not work. You get an error "Incorrect syntax near '='."

It's the 2=2 check that causes this fail. Instead, you could write this as:

Select 1
Where  2 = Case When 1=1 Then 2 End

This code actually works and returns a single row/single column with value = 1 (as expected). Notice that the 2=2 was removed. In this situation, the case expression can only return 1 or 2 values (NULL or 2). Since this is testing 1=1, it will always return the value 2, but that's not the point. The point is... the CASE/WHEN statement only returns a value.


Many programming languages allow you to do an in-line expression evaluation. For example, in VB you can do this:

VariableA = (1=1)

When evaluated, VariableA would be a boolean with a value of true.

If you tried this in SQL:

Declare @VariableA Bit

Set @VariableA = (1=1)

You get an error, "Incorrect syntax near '='."

The only way to do this in a SQL query is with the Case/When statement, like this:

Declare @VariableA Bit

Set @VariableA = Case When 1=1 Then 1 Else 0 End

Select @VariableA

Note that I said, "In a sql query". If you wanted to write multiple queries, you can use IF, like this.

If 1=1
	Set @VariableA = 1
Else
	Set @VariableA = 0

Of course, this is now multiple sql queries instead of just 1.

The 2 methods that Frederico show are the 2 most common ways to accommodate this situation. There are other ways. For example, you could use dynamic sql where you build a string of sql within a procedure and then execute it. Alternatively, you could "double up" your conditions, like this:

Declare @Temp Table(id int, EyeColor VarChar(20), HairColor VarChar(20))

Insert Into @Temp Values(1, 'brown','brown')
Insert Into @Temp Values(2, 'brown','Blond')
Insert Into @Temp Values(3, 'blue', 'brown')
Insert Into @Temp Values(4, 'blue', 'blond')

Declare @EyeColor VarChar(20),
        @HairColor VarChar(20)

--Set @EyeColor = 'blue'
Set @HairColor = 'Blond'

Select *
From   @Temp
Where  1 = Case When @EyeColor  Is Not NULL And EyeColor  = @EyeColor  Then 1
                When @HairColor Is Not NULL And HairColor = @HairColor Then 1
                End

Note that if @EyeColor is not null, then that condition will apply and hair color will not be evaluated.



-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