CODEWITHSUNDEEP
sqlsql-server-2008where-clause
Gil Peretz
Gil Peretz

Reputation: 2419

SQL Where clause with dynamic IN() operator

I have a query which should be later used to build an SSRS-2008 report. This query is going to be used as a generic template for many reports.

Because of that, the query have lots of parameters in it, and each report should configure the parameters accordingly in the Report Builder.

After this long intro, my issue is that:

In the query's WHERE clause the parameters are with IN() operator, for example:

WHERE Users IN(@p1) AND ...

the @p1 in one report can be specific users (e.g. WHERE Users IN(121,154,151) ), and in different report it can be all users . In that case ,and this is my question, what is the value that the parameter @p1 should get ?

I know I can use different data set in order to retrieve all the users and put it in the parameter, but I want to avoid that (due to performance issues and keep the query as generic as possible) and want to know if there is any other options ? (something like IN('% %') )

Thank you.

Upvotes: 1

Views: 1369

Answers (2)

Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13582

First of all I like your picture which is full of love and wish you a happy life.

After that you may find This same question of mine about list-like parameters useful, I asked it a couple of days ago and came up with pretty nice and various solutions.

hope it helps.

Upvotes: 2

syed mohsin
syed mohsin

Reputation: 2938

You can do this by just changing the parameters

  @p1 = "SELECT users FROM table"

Upvotes: 1

Related Questions