CASE in a where clause for Stored proc 1

[CJwater]

Hi!,

I'm passing in an optional parameter to a store proc and would like to add it to the were clause if the optional parameter is not NULL. (This case does not work)

/* @Allowance is my optional parameter
WHERE
dateYY Between @StartYr And @EndYr
and ACC_No Between '25000000' And '28000000'

CASE WHEN @Allowance is not null then
AND Job_Desc = @Allowance
END

Can someone tell me what I'm doing wrong and if I can have a case in the where clause?
Thanks

 

[gmmastros]

You can have a case in a where clause, but not the way you wrote it (obviously, or you wouldn't be asking the question).

Try this...

WHERE
    dateYY Between @StartYr And @EndYr
    and ACC_No Between '25000000' And '28000000'
    [!]And (@Allowance Is NULL or Job_Desc = @Allowance)[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CJwater]

Thank You! It's seem so obvious now looking at "your" answer. I couldn't see the trees from the forest...

 

[JCS2008]

WHERE
dateYY Between @StartYr And @EndYr
and ACC_No Between '25000000' And '28000000'
And Job_Desc = isnull(@Allowance,Job_Desc)

if the Job_Desc is null the it will be like this:

Job_Desc = Job_Desc wich is always true so in fact it will remove the restriction

I used the built in function ISNULL but you can also use Coalesce()

 

[gmmastros]

JCS2008,

That will not work for NULL values of Job_Desc becuase NULL does not compare to NULL.

Ex:

[COLOR=green]-- Create a table variable to test this functionality
[/color][COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](JobDesc [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Bus Boy'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Waitress'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Cook'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](NULL)

[COLOR=green]-- Create a variable that mimics search criteria
[/color][COLOR=green]-- Set the variable to NULL which represents the
[/color][COLOR=green]-- condition where we want to return all data
[/color][COLOR=blue]Declare[/color] @JobDescription [COLOR=blue]VarChar[/color](20)
[COLOR=blue]Set[/color] @JobDescription = NULL

[COLOR=green]-- My original query that correctly returns the NULL record
[/color][COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  (@JobDescription [COLOR=blue]Is[/color] NULL Or JobDesc = @JobDescription)

[COLOR=green]-- This does not return the NULL row because NULLS don't compare
[/color][COLOR=green]-- to other NULLs
[/color][COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  JobDesc = [COLOR=#FF00FF]IsNull[/color](@JobDescription, JobDesc)

I encourage you to copy/paste this to Query Analyzer and run the code. You will see that the IsNull test does not return the row with NULL (like it should).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom