A WHERE clause that always selects all records in a table

Question

I'm looking to create a function that generates an SQL query, filtering the results by an expression given as a parameter (which will passed as a 'WHERE' clause). If the parameter is omitted, all results are returned.

In order to do this I can test for a null parameter and only build the where clause if parameter is given (where = (parameter != null) ? "" : "WHERE " + parameter). However, I was thinking is there an expression that I can default to that will always return all results. This way I don't need to test to see whether to include the WHERE keyword (where = "WHERE " + parameter).

I've intentionally not mentioned escaping the parameter to avoid injection. I won't forget this in my solution, I promise! :)

What language are you using? Or are you using proprietary SQL language (e.g. T-SQL) to generate the query? Please add tags — PinnyM, Feb 24 '12 at 14:51
It is a proprietary SQL language, specific to the application I'm working with. It's quite possible that there may not be a way to do this, but I thought I'd find out the 'standard' way of doing this as a starting point. — Dan Stevens, Feb 24 '12 at 15:21

score 9 · Accepted Answer · answered Feb 24 '12 at 14:52

9

I usually use 1 = 1 for that.

answered Feb 24 '12 at 14:52

ruakh

175,680
26
273
307

score 4 · Answer 2 · answered Feb 24 '12 at 14:52

4

(where = "WHERE 1 = 1 " + parameter)

In this case parameter should start with " AND " token.

answered Feb 24 '12 at 14:52

Alberto De Caro

5,147
9
47
73

score 1 · Answer 3 · answered Feb 24 '12 at 14:51

1

You can initialize the parameter as TRUE or 1.

answered Feb 24 '12 at 14:51

Pulkit Goyal

5,604
1
32
50

3

Not all DBMSes support that, and the OP doesn't specify what DMBS (s)he's using. – ruakh Feb 24 '12 at 14:53

A WHERE clause that always selects all records in a table

3 Answers3