SELECT with varying WHERE clause

Question

I have the following Table (C# and MS Access):

_sSqlString = "CREATE TABLE Device("
                                            + "deviceID AUTOINCREMENT NOT NULL,"
                                            + "brandName VARCHAR NOT NULL,"
                                            + "modelName VARCHAR NOT NULL,"
                                            + "deviceCaliber VARCHAR NOT NULL,"
                                            + "batterySize INT NOT NULL,"
                                            + "screenSize DOUBLE NOT NULL,"
                                            + "frontCameraMP INT NOT NULL,"
                                            + "rearCameraMP INT NOT NULL,"
                                            + "weightedScore INT,"
                                            + "PRIMARY KEY (deviceID)"
                                            + ")";

Table in MS Access with Populated Data

Goal:

The User can select the Brands they prefer, which will do a SELECT command to find all devices that matches their preference. The User can also select "Any Brand", which is a simple SELECT * command.

User Interface Screenshot 1

User Interface Screenshot 2

What I have tried:

Successfully: A SELECT procedure which passes in all the Brands, set to NULL if they are not selected.

While this works, I feel the repetition of OR is inefficient, and has the potential to be improved. Is it possible to improve this, or is a SELCT OR, OR, OR e.t.c the best way?

Thank you in advance for any help, it's very much appreciated! :)

"A SELECT procedure which passes in all the Brands, set to NULL if they are not selected." I don't know what this means. Why not include the actual code in your question? — John Wu, Dec 02 '20 at 20:05

score 1 · Answer 1 · answered Dec 02 '20 at 20:07

1

Would using IN be appropriate here?

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15

I'm not sure how you're going from C# to SQL here, but if you're just executing SQL you could concatenate a string of selected brands and use that in your select. Point being you want a select that looks something like

SELECT *
FROM Device AS d
WHERE d.brandName IN ('Apple', 'Alcatel')

answered Dec 02 '20 at 20:07

Blue

163
1
12

1

You should NOT create an SQL statement by concatenating user input as this makes your code vulnerable to SQL injection.See [here](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) for alternatives. – Klaus Gütter Dec 02 '20 at 20:20

SELECT with varying WHERE clause

1 Answers1