How do you search between two columns using a parameter and a case when statement? SQL Server

Question

I would like to search between to columns in a s query or a table depending on the variable on a paramenter e.g

Declare @SelectAll as interger
Set @SelectAll = 1
Declare @Column as interger

Select mt.Column1, mtColumn2 
From MyTable as mt
Where Case When @SelectAll = 1 Then 
           mt.Column1 IN(@Column) and mt.Column2 (' Selecting all")
           When @SelectAll = 1 Then 
           mt.Column2 IN(@Column) and mt.Column1 (' Selecting all")
           End 

The purpose of this query is to allow the user to search between the column they choose. Further more the use of parameter is for the purposes of writing reporting services reports.

Answer 1

How many Columns do you have? If not many I would just hard code all of the possible combinations in to a stored procedure and select the right one with conditional logic testing IF (@Column = 1) etc. The only alternative is to use dynamic SQL I think. Trying to create one query that does it all you will just end up with issues where reuse of the execution plan for one case causes you performance issues in another case.

Answer 2

I have found a solution that best suits me by add this expression on the where clause

(@SelectAll = 1 AND mt.Column1 IN(@Column)) OR (@SelectAll = 2 AND mt.Column2 IN(@Column)) OR (@SelectAll = 3)