CASE in Stored Proc

[southbean]

Hello All,
I’ve been hacking away at a stored procedure for a while with no success. Time to ask the experts.
I have a form that uses this query to pull two values from a string. User chooses one value.

SELECT DISTINCT
	Field =
	CASE 
		WHEN SUBSTRING(Field, 1, 2) IN 
			('A1', 'B1','C1','D1','E1','F1','G1') THEN 'Value1'
		ELSE 'Value2'
	END
FROM
	Table1

I have a variable called @Var1. I’m trying to use a CASE statement in the WHERE clause of my stored proc to

            CASE  
		WHEN @Var1 = 'Value1’ THEN 
			SUBSTRING(Table. Field, 1, 2) IN ('A1','B1','C1','D1','E1','F1','G1')
		ELSE 
			SUBSTRING(Table. Field, 1, 2) IN ('H1','I1','J1','K1','L1','M1','N1')
            END

It’s not working. Any comments/suggestions would be greatly appreciated!

- tl

 

[RiverGuy]

WHERE
(@Var1 = 'Value1' AND SUBSTRING(TableName.Field, 1, 2) IN ('A1','B1','C1','D1','E1','F1','G1'))

OR

(ISNULL(@Var1, '') <> 'Value1' AND SUBSTRING(TableName.Field, 1, 2) IN ('A1','B1','C1','D1','E1','F1','G1'))

 

[southbean]

Hello RiverGuy,

Thank you for your reply!

I now get data returned! Excellent!

However, I only get data for 'Value1', even if I select 'Value2'. Any thoughts?

Again, thank you very much for your reply!

- tl