Using parameters in a Where clause

Question

I have not found an existing thread for exactly what I need. I have a reporting tool that allows me to ask user for input (Client) and pass to SQL as a parameter.

There could be multiple Clients or the word All. If parameter contains 'All' I need to select all records. If the parameter does not contain 'All' I only need to select the records in the parameter.

I found a few solutions that work if the parameter is only one value but mine is a list and needs to use the 'in' predicate. (I think)

Any thoughts?

Please tag your RDBMS - MySql? Sql-Server? More often than not you should handle listed parameters in your code, like [this](https://stackoverflow.com/questions/9384446/how-to-pass-sqlparameter-to-in). Otherwise your alternative is dynamic SQL which I rarely suggest. — Tyler Roper, Jun 28 '17 at 14:31
This will likely require features of your reporting tool. What is it? — rd_nielsen, Jun 28 '17 at 14:39
Sanit - Intersystems Cache and I would be interested in seeing a dynamic SQL suggestion rd - Its not well known I don't think. It is called Visual Data Miner and does have some features to handle this but I would like this to be done on the server side which is why I am hoping to find an SQL solution — CoSpringsGuy, Jun 28 '17 at 14:44

score 2 · Accepted Answer · answered Jun 28 '17 at 16:24

2

Without knowing your datamodel, something like the following should do:

SELECT *
FROM clients
WHERE ID IN (@clients) OR 'All' IN @clients

answered Jun 28 '17 at 16:24

Menno

11,415
13
54
86

2

Worked like a champ! – CoSpringsGuy Jun 28 '17 at 20:43
1

@CoSpringsGuy Glad I could help. In that case you could / should flag this answer as the accepted answer for future reference. – Menno Jun 28 '17 at 20:58

score 2 · Answer 2 · answered Jun 28 '17 at 20:43

When you speak about Caché, it means that you even have multiple ways how you execute your query. And also there are some ways how to achieve it. I would suggest to use different queries, for ALL records and for chosen. And you can use IN or %INLIST predicate.

Using parameters in a Where clause

2 Answers2