We help IT Professionals succeed at work.

Conditional where clause

AbeSpain
AbeSpain asked
on
Hi, I'm sure I have had a solution to this problem before but I just can't find it. Anyway, I want to only include certain parts of a where clause if the data I am testing is of a cetain value. The best way for me to explain it is to include some code (that doesnt work but should help).

Where
      ((If @JobsIndustryiD > 0) JobsIndustryiD = @JobsIndustryiD)
And
      ((If @JobVerticalMarketiD > 0) JobVerticalMarketiD = @JobVerticalMarketiD)
And
      ((If Len(@JobVerticalMarketiD) > 0) JobVerticalMarketiD = @JobVerticalMarketiD)
Comment
Watch Question

Top Expert 2008

Commented:
you can write it like this:

Where
      ((@JobsIndustryiD <= 0) OR (JobsIndustryiD = @JobsIndustryiD))
And
      ((@JobVerticalMarketiD <= 0) OR (JobVerticalMarketiD = @JobVerticalMarketiD))
And
      ((Len(@JobVerticalMarketiD) <= 0) OR (JobVerticalMarketiD = @JobVerticalMarketiD))

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Hello AbeSpain,

Where
      ( ( @JobsIndustryiD > 0)  AND JobsIndustryiD = @JobsIndustryiD) )
And
      (( @JobVerticalMarketiD > 0)  AND JobVerticalMarketiD = @JobVerticalMarketiD) )
And
      (( Len(@JobVerticalMarketiD) > 0)  AND  JobVerticalMarketiD = @JobVerticalMarketiD)  )





Aneesh R
You can do away with most of your ( ) and you dont need to use teh LEN function but you may need to use ISNULL(@JobVerticalMarketiD,'')

WHERE
      (@JobsIndustryiD <= 0 OR JobsIndustryiD = @JobsIndustryiD)
AND
      (@JobVerticalMarketiD <= 0 OR JobVerticalMarketiD = @JobVerticalMarketiD)
AND
      (@JobVerticalMarketiD = '' OR JobVerticalMarketiD = @JobVerticalMarketiD)

Cheers, Andrew
you can try select inside the where.

figure out your best choice.

waiting for your reply.

Author

Commented:
All the above seem fine, they are all pretty much the same. Will come back shortly to close the question.
Top Expert 2008

Commented:
@AbeSpain hi. just want to know, did my solution not work?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.