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 parameter e.g

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

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.

Have any of your previous questions been answered satisfactorily? If so you should return to them and start marking accepted answers! — Martin Smith, May 11 '10 at 09:36

score 0 · Accepted Answer · answered May 11 '10 at 09:34

0

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.

answered May 11 '10 at 09:34

Martin Smith

438,706
87
741
845

I will be unable to use store procs because using them is against the company policies i am working. Furthermore it is always a bad idea using hard coding. – Kip Real May 11 '10 at 09:43
The number of colunms 2 and the values that will be searched for are not constant – Kip Real May 11 '10 at 09:48
Sounds an odd company policy. The conditional logic could equally well live in the Reporting Services report though refactoring the database would be trickier with all this logic scattered around in different reports. I don't agree with your last point. For an example of the issues that arise see this thread http://stackoverflow.com/questions/2788082/sql-server-query-performance/2788160#2788160 – Martin Smith May 11 '10 at 09:52
RE: "The number of colunms 2 and the values that will be searched for are not constant " It sounds like you are going to be better off using dynamic SQL to create the query. I thought Reporting Services needed a consistent format of the result set though? – Martin Smith May 11 '10 at 10:02
Unfortunately for me Dynamic SQL is also unexceptable in the organisation. I wanted to use conditional where clause. IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] But this will mean i will have to repeat my sql_statement – Kip Real May 11 '10 at 10:10
@Martin Smith what do you think about the statement below – Kip Real May 12 '10 at 12:19

score 0 · Answer 2 · answered May 11 '10 at 14:29

0

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)

answered May 11 '10 at 14:29

Kip Real

3,319
4
21
28

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

2 Answers2