Using a Parameter to change filter from = to in 1

[IanWaterman]

This is my failed attempt at a Where clause

where (case when @SelectGL = 'Client Account' then TXN_GLCODE_FK = 211 else TXN_GLCODE_FK in (353, 355) end)
and TXN_AMOUNT <> 0
and cast(TXN_CREATED_DATE as date) >= @StartDate
and cast(TXN_CREATED_DATE as date) <= @Enddate

I know the use of case is wrong. I am trying to to build logic that when

@SelectGL = 'Client Account' then TXN_GLCODE_FK = 211

or

@SelectGL = 'Interest' then TXN_GLCODE_FK in (353, 355)

Any suggestions

Thank you

Ian

 

[gmmastros]

Where 
  (
    (@SelectGL = 'Client Account' And TXN_GLCODE_FK = 211)
    or
    (@SelectGL = 'Interest' And TXN_GLCODE_FK in (353, 355)
  )
and TXN_AMOUNT <> 0
and cast(TXN_CREATED_DATE as date) >= @StartDate
and cast(TXN_CREATED_DATE as date) <= @Enddate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think I missed a parenthesis:

Where 
  (
    (@SelectGL = 'Client Account' And TXN_GLCODE_FK = 211)
    or
    (@SelectGL = 'Interest' And TXN_GLCODE_FK in (353, 355))
  )
and TXN_AMOUNT <> 0
and cast(TXN_CREATED_DATE as date) >= @StartDate
and cast(TXN_CREATED_DATE as date) <= @Enddate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

Thanks George! Really obvious soluition if I thought about it a bit more.

Ian

 

[gmmastros]

The best solutions are the simple ones. Glad you got it working.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

Since @SelectGL is a constant is it possible formatting like below could be more efficient?

DECLARE @ValidInt1 = 211, @ValidInt2 = 211

IF (@SelectGL = 'Client Account') BEGIN
	SET @ValidInt1 = 353
	SET @ValidInt2 = 355
END

...
WHERE TXN_GLCODE_FK IN (@ValidInt1, @ValidInt2)
...