IN, LIKE and Wildcards

Question

Post reply

IN, LIKE and Wildcards

rolus

Mr or Mrs. 500

Points: 518
More actions
January 20, 2006 at 12:16 pm

#170718

I am currently using Reporting Services, I am trying to return 'ALL' records if a user has a certain level of access to a database table.
My SQL is as follows it is in the WHERE clause.
...AND WHERE DEPTID IN (@BU) OR LIKE @BU
Now @BU can either be an Array of Strings ('BU1','BU2','BU2','BU3','BU4') or a WILDCARD '%'
When an array is returned I get an error that the right parentheses is missing, when the array is a wildcard (%) it works.
Question, how can I do a conditional Where statement?
If @BU is a wildcard then use the like operator
If @BU is an Array then use the IN operator.
Is there a Wildcard to use in an "IN" statement? OR a LIKE statement that act like an "IN" statement.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

How about:

WHERE CASE WHEN @BU = '%'

THEN DEPTID LIKE @BU

ELSE DEPTID IN (@BU)

END

Test it first..

-SQLBill

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>If @BU is an Array then use the IN operator.

You can't use a variable containing a comma-separated list directly with IN().

You either need to

- Construct dynamic SQL

- Parse the list into a table, and join the table to your query

rolus Mr or Mrs. 500 Points: 518 More actions · Answer 3

I am using a dynamic select query in reporting Services. The CASE statement doesn't work, I tried that before but thought maybe the syntax was wrong. This is a real pain and really shows the weakness of SQL, I am sure that it could work in a StoredProc or function, since I don't have access to these I will persue other means. I am using reporting services, so I am going to investigate writing some VB code to preprocess the SQL somehow.

Thanks for the help, I appreciate the feedback.

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Yes, this is one of the things you can not do in SQL. However, I wouldn't say that it shows its weakness - it merely shows that SQL works differently than Basic and other procedural languages.

Here one example how you could solve this problem... Instead of passing array in a form of comma delimited list, create a table variable with one column, fill it with the array values and pass that variable to the SQL.

DECLARE @BUList TABLE (BU varchar(10))

INSERT INTO @BUList VALUES ('BU1')

INSERT INTO @BUList VALUES ('BU2')

INSERT INTO @BUList VALUES ('BU3')

INSERT INTO @BUList VALUES ('BU4')

...AND WHERE (DEPTID IN (select BU from @BUList) OR (select top 1 BU from @BUList = '%'))

In fact, a table variable gives you a possibility to use JOIN instead of the IN clause (IN takes more time to process if there are too many values). So, if you would be able to prepare the data in VB so that you can use table variable, then it should work fine. I suppose that '%' means ignore BU criteria, and it is so that if you want to ignore BU, you pass precisely this string (not 'BU%', not several values one of which is '%', but '%' as the only value of the entire array) so I didn't use LIKE... "Is equal" is better for performance than LIKE.

HTH, Vladan