Better way to construct an SQL query with multiple wheres which hit the same column

Question

My goal here is to create a dynamic 'report builder' application without creating a string to pass to the server as my sql query. I am wondering if there is a good (or any) way to make a where clause that has an unknown amount of possibilities. The best way I can explain is with this query...

SELECT *
FROM table
WHERE column1 = 'a' OR column1 = 'b' OR column1 = 'c' OR column1 = 'd' ...

Basically just an unknown amount of ORs. I know the following query is in no way close to correct, but here's what I am looking for an idea of:

@ColValues = 'a || b || c || d ...'

SELECT *
FROM table
WHERE column1 = @ColValues;

Essentially I want the user to pass only one variable for column1 and still be able to return results based on multiple where clauses. Normally the IN operations would solve my issue, problem is with this application I have no clue how many items will need to be in the final where clause. Could be 1 value, could be 50. Also needs to be passed from a C# parameter to a T-SQL stored procedure.

score 2 · Answer 1 · edited May 23 '17 at 12:07

2

If you are strictly talking about SQL then better you can do is store the IN elements in a table type variable and then do a JOIN with them. Something like

declare @tabin table(element char(1));

insert into @tabin
select 'a'
union
select 'b'
union
select 'c'
union
select 'd';


SELECT t1.*
FROM table t1
JOIN @tabin t2 ON t1.column1 = t2.element;

Note: What you trying to achieve can be done by following the example shown in this post C# SQL Server - Passing a list to a stored procedure

edited May 23 '17 at 12:07

Community

1
1

answered Nov 12 '14 at 19:46

Rahul

76,197
13
71
125

what if I can't know how many values there will be? I have to programatically fill `@tabin`, can't hard code it. – Volearix Nov 12 '14 at 20:07
In other words, I have to pass the user desired variables and generate the table from this. Could be just `'a'` or could be `'a', 'b'` and so on. – Volearix Nov 12 '14 at 20:08
You don't need to hardcode it. This can be done programmatically as well in C#. there are lots of example present about how you can achieve this. See edit in answer (Specially the link provided). – Rahul Nov 12 '14 at 20:14

score 1 · Answer 2 · answered Nov 12 '14 at 19:45

1

Try something like this

SELECT *
FROM table
WHERE column1 IN ('a', 'b', 'c', 'd', ...)

answered Nov 12 '14 at 19:45

Scoregraphic

7,110
4
42
64

Okay, the `IN` keyword doesn't exactly work here. Maybe I should clear it up in my description, but the user is going to pass a parameterized variable for the where clause. – Volearix Nov 12 '14 at 19:59

score 0 · Answer 3 · answered Nov 12 '14 at 19:45

0

Depending on the server the following might work:

SELECT * 
FROM table
WHERE column1 IN ('a', 'b', 'c')

Since you tagged as T-SQL, here's MSDN on WHERE ... IN: http://msdn.microsoft.com/en-us/library/ms177682.aspx

answered Nov 12 '14 at 19:45

Jarek Piórkowski

638
3
8

Re: your follow-ups, depending on how you're accessing the SQL database, your query parametrization might handle this for you: `SELECT * FROM table WHERE column1 IN @param` and specify the @param to be a List/IEnumerable/etc – Jarek Piórkowski Nov 12 '14 at 20:12

score 0 · Answer 4 · answered Nov 12 '14 at 19:46

0

Maybe you want the IN keyword

SELECT *
FROM table
WHERE column1 IN ('a','b','c', ...)

answered Nov 12 '14 at 19:46

cpacheco

186
9

score 0 · Answer 5 · answered Nov 12 '14 at 19:47

0

Stick all of your where's into a temp table and do:

SELECT * 
FROM table
WHERE column1 IN (SELECT colVals from #temp)

answered Nov 12 '14 at 19:47

LeeG

708
5
14

This is going in the right direction I think, can you pass a table as a variable from C# to T-Sql? – Volearix Nov 12 '14 at 20:01

score 0 · Answer 6 · answered Nov 12 '14 at 20:03

@Rahul's answer is the best if you want to dynamically (programmatically) create an array.

@Scoregraphic's answer is best if you know the values when writing the SQL statement (many others have repeated the same answer he's given).

This answer's more for novelty value to show how you could do this by amending your guess. Potentially useful if your search string was passed in to a function as a single string value rather than a list; but otherwise I really wouldn't recommend this approach:

declare @ColValues nvarchar(100) = 'a || b || c || d'

declare @table table(column1 nchar(1))
insert @table select 'a'
union select 'd'
union select 'h'

SELECT *
FROM @table
where '|| ' + @ColValues+ ' ||' like '%|| ' + column1 + ' ||%'

Better way to construct an SQL query with multiple wheres which hit the same column

6 Answers6