Need help with SQL Query that should determine whether or not to use a WHERE clause

mainrotor
mainrotor used Ask the Experts™
on
Hi Experts,
I need help rewriting my SQL Server query to determine whether or not to include a WHERE clause.
I have included my query below.

I want the WHERE clause to be included whenever the @Team variable is greater than 0, and exclude it if it is 0.

How can this be done?

Current Query:
Current query being used
thank you for your help,
mrotor
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>I want the WHERE clause to be included whenever the @Team variable is greater than 0, and exclude it if it is 0.
WHERE clauses don't 'do' exclusion, so you have to do an OR clause to compare..
WHERE (k.TeamID = @Team AND @Team > 0) OR (@Team = 0)

Open in new window


Also next time please post your code in a code block so we can copy-paste and work with it if needed..
Or here is another approach:

where 1 = case when isnull(@Team, 0) = 0 then 1 else case when k.TeamId = @Team then 1 else 0 end end

Open in new window

Author

Commented:
Thank you for your suggestions.  I will try them.

Jim, I will post my code next time.


mrotor

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial