SQL query using IN in where clause

Question

Was trying to figure out (how) if I can use a list of "filter" type of string and get it working with IN in WHERE clause. I had below (and it is not working of course):

SELECT * 
FROM Table1 
WHERE Table2.[Filter String]

[Filter String] here is actually a list and stores various working but wildcard criteria formats, such as:

[Facility] Like '*CityName*'

or

[Facility] Like '*CityName*' AND [ProductionLine] Like 'LineName*'

Most use cases from other threads, usually working with IN ('a', 'b', ...) or

IN (SELECT * FROM table WHERE field=xx etc...

In my case, had to work with a data field with "changing criteria" upon users' entries. So how would you structure this SQL query?

Something like that: http://stackoverflow.com/a/1865426/1258492 — RAY, Aug 17 '16 at 03:21

Pekka · Answer 1 · 2016-08-24T16:31:24.900

1

You appear to be attempting to use a query with a dynamic selection clause.

This is not possible in a simple SQL query as the SQL DML only permits parameterised values in the terms of an expression.

In SQL server, you can create a query of the type you suggest with the EXECUTE statement. It can accept a character string parameter that is evaluated when the query is run.

Note that this will often result in a performance penalty as the query execution plan is established and compiled each time. A parameterised query will not require this overhead.

edited Aug 24 '16 at 16:31

answered Aug 17 '16 at 03:36

Pekka

3,529
27
45

@ Pekka, thanks for the information. This is unfortunately in MS Access 2010 environment. Was hoping if there's a work around. – Melvin Chung Aug 17 '16 at 14:04
@Melvin You could require the user to record a list of values in another table, and then join on its contents. This quickly blows up into a major mission through (consider multiple users, same user from multiple sessions) and may not be attractive. You can, of course, also filter the result set on the client if the total volume is not manageable. If you have VBA in the application, you could generate the queries in the code - always avoiding the SQL injection problems. – Pekka Aug 24 '16 at 16:33

score 0 · Answer 2 · answered Aug 17 '16 at 03:20

0

If I understand correctly, you can use a subquery:

SELECT t1.*
FROM Table1 as t1
WHERE EXISTS (SELECT 1
              FROM Table2 as t2
              WHERE t1.facility LIKE t2.[Filter String]
             );

answered Aug 17 '16 at 03:20

Gordon Linoff

1,242,037
58
646
786

@ Gordon, [Filter String] here is a list with dynamic field and value (ie, filter type of string) – Melvin Chung Aug 17 '16 at 14:01

score 0 · Answer 3 · answered Aug 17 '16 at 05:03

0

May be Below query help to solve your problem:

declare @Query varchar(max)
set @Query = 'SELECT * FROM Table1 WHERE Table2.'+ @Column_Name  +' in ('+ @Value_List +')'
exec(@Query)

answered Aug 17 '16 at 05:03

chirag satapara

1,947
1
15
26

SQL query using IN in where clause

3 Answers3