Adding items to WHERE Statement based on variables

Transact-SQL
1

Hey friends! After much Googling, I am reaching out to the masters here at SQLTeam for some help.

I am attempting to add a line to the WHERE clause of a query based on a variable instead of totally re-writing the entire WHERE clause based on the variable. So in the Example below there is a SELECT from a few tables and joins and then the WHERE clause. In the WHERE there is a comment of CASE WHEN OH. What I am hoping to do right below the comment is say if the variable @State = 'OH' then add this to the WHERE: month(a.birth_date) = @month. Otherwise, do not include that in the WHERE. Hopefully there is a way to accomplish this. Otherwise I have to do something like IF @State = 'OH' do the whole query with that in the WHERE clause ELSE do the whole query again without it. Thanks a lot for any help and insight anyone can provide. It is greatly appreciated. I haven't been here in awhile, but SQLTeam is great!! Thanks again friends!

Here is an example:

SELECT 'many columns'
FROM SeveralTables
WHERE (b.status = 'A' 
		AND b.apptstate = @state 
		AND cast(b.company_code as char(1)) LIKE CASE @line 
						WHEN 'A' THEN '1' 
						WHEN 'B' THEN '2'
						END
		/* CASE WHEN OH */
		AND (CASE @State 
				WHEN 'OH' 
				THEN month(a.birth_date) = @month 
			 END)
		AND b.sales_quote_tracking <> 1
2 
and (@State!='OH'
 or  month(a.birth_date)=@month
    )
1 Like
3

Looks odd, but it works:

SELECT 'many columns'
FROM SeveralTables
WHERE (b.status = 'A' 
		AND b.apptstate = @state 
                --you never cast/convert a table column unless it's absolutely unavoidable
		AND b.company_code = CASE @line 
						WHEN 'A' THEN '1'
						WHEN 'B' THEN '2'
						END
		AND (State <> 'OH' OR State IS NULL OR month(a.birth_date) = @month)
		AND b.sales_quote_tracking <> 1
1 Like
4

Hey guys! Thanks so much! Both solutions provided worked perfectly!

My appreciation is beyond all measure!

Seems so simple now, but was not getting there on my own or with Google.

Thanks again SQLTeam!!

5

You need to second one if your [State] column is "nullable", that is to say if it is permitted to store a NULL in that column