Use CASE in WHERE clause to handle SELECT ALL

Question

Post reply

Use CASE in WHERE clause to handle SELECT ALL

Joe Salvatore

SSCrazy

Points: 2174
More actions
May 26, 2004 at 9:12 am

#162019

Have a proc that I want to perform selection on a value only if specified in the proc parameter. The following keeps getting a Server: Msg 170, Level 15, State 1, Line 192 Line 192: Incorrect syntax near '>'. Error.
WHERE
CASE WHEN @AddressNumber = 0 THEN
(F06136.YZDTEY >= @FromYear
AND F06136.YZDTEY <= @ToYear
AND (F06136.YZAN8 > 0)
AND (F06136.YZTARA = 'FEDERAL')
AND (F06136.YZPTAX = 'C'))
ELSE
(F06136.YZDTEY >= @FromYear
AND F06136.YZDTEY <= @ToYear
AND (F06136.YZAN8 = @AddressNumber)
AND (F06136.YZTARA = 'FEDERAL')
AND (F06136.YZPTAX = 'C'))
END
Is there a way to correct this and avoid the syntax error or should I consider another approach? Goal is to either use the specified @AddressNumber for selection criteria in the WHERE clause or to select all records without regard for AddressNumber when the @AddressNumber is not valued (or valued 0).
THANKS for any insights!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

draisin SSC Enthusiast Points: 112 More actions · Answer 1

I think you just need to include an alias, such as:

CASE

....

END as AddressNumber

Joe Salvatore SSCrazy Points: 2174 More actions · Answer 2

Made mod to add 'as AddressNumber' to end of CASE... same error received. Any other thoughts?

Antares686 SSC Guru Points: 125444 More actions · Answer 3

First I will have to assume all AddressNumbers are > 0 anyway. So try this

WHERE

F06136.YZDTEY >= @FromYear

AND F06136.YZDTEY <= @ToYear

AND (F06136.YZTARA = 'FEDERAL')

AND (F06136.YZPTAX = 'C')

AND (F06136.YZAN8 > 0)

AND ((CASE WHEN @AddressNumber = 0 THEN @AddressNumber ELSE F06136.YZAN8 END) = @AddressNumber)

Joe Salvatore SSCrazy Points: 2174 More actions · Answer 4

THANKS FORUM for all the efforts.

I had to change the @AddressNumber to 1 in order for the CASE statement in the WHERE clause to work using Antares636 proposed modifications. Previously this was evaluating as 0=0 and not returning any records. By setting to 1, when the @AddressNumber parameter is passed as blank or 1, then the CASE statement in the WHERE clause evaluates to 1=1 (aka all records).

THIS WORKED WONDERFULLY!!!!