Help with Query... SELECT .... IN (Concatenated string)

Question

Post reply

Help with Query... SELECT .... IN (Concatenated string)

nns80

Mr or Mrs. 500

Points: 524
More actions
February 24, 2004 at 3:09 pm

#107827

I am trying to perform a search on a table based on the given criteria. However it is returning me no rows, which it should in the normal scenario.
DECLARE @status AS VARCHAR(20)
SET @status = char(39) + 'ER' + char(39) +', ' + char(39) + 'OK' + char(39)
/*@Status = 'ER', 'OK' */
SELECT * FROM TABLEA WHERE TABLEA.Status IN (@Status)

Please advice.
Thanks,
NS.

Viewing 8 posts - 1 through 7 (of 7 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

DECLARE @status AS VARCHAR(20)

SET @status = char(39) + 'ER' + char(39) +', ' + char(39) + 'OK' + char(39)

/*@Status = 'ER', 'OK' */

SELECT * FROM TABLEA WHERE charindex(',' + TABLEA.Status + ',', ',' + @status+ ',') > 0

Be adviced that for Large Tables this method is NOT fast

EASY TO TYPE THOUGH

* Noel

nns80 Mr or Mrs. 500 Points: 524 More actions · Answer 2

I dont think the first option will help me. I tried doing it but didnt get the desired result.

In fact the @status can have N number of concatenated values like 'ER1','ER2','OK' . So I am looking for a generic way to return all those rows whose status belongs to the @status group of values.

Thanks,

NS.

hoo-t SSChampion Points: 12713 More actions · Answer 3

another option...

DECLARE @status AS VARCHAR(20)

SET @status = char(39) + 'ER' + char(39) +', ' + char(39) + 'OK' + char(39)

/*@Status = 'ER', 'OK' */

exec('SELECT * FROM TABLEA WHERE TABLEA.Status IN (' + @status + ')')

wz700 SSCertifiable Points: 7133 More actions · Answer 4

A temp table to hold ER, ER2, OK and join with searching table would be much easier.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

The problem here

DECLARE @status AS VARCHAR(20)

SET @status = char(39) + 'ER' + char(39) +', ' + char(39) + 'OK' + char(39)

/*@Status = 'ER', 'OK' */

SELECT * FROM TABLEA WHERE TABLEA.Status IN (@Status)

is that you exect the IN to see it @status as 'ER', 'OK' but it actually sees it as '''ER'', ''OK'''

So it is looking for the existance of ('ER', 'OK') in the string not 'ER' OR 'OK'

The dynamic SQL method used by hoo-t is the most common method but the one with the highest potential for injection attacks.

Is suggest use wz700's suggestion create a temp table with one column and pop with your values.

CREATE TABLE #x (

col1 char(2)

)

INSERT #x VALUES ('ER')

INSERT #x VALUES ('OK')

SELECT * FROM TABLEA WHERE TABLEA.Status IN (SELECT col1 FROM #x)

or optionally

SELECT * FROM TABLEA

INNER JOIN #x x

ON

TABLEA.Status = x.col1

Then

DROP TABLE #x

(may want to also include a check for #x to be dropped at begining for safety)

That is more secure and performs grandly.

noeld SSC Guru Points: 96590 More actions · Answer 6

the reason you didn't get the results with my post was that @status should had been set to

Set @status = 'ER,OK'

instead of

Set @status = char(39) + 'ER' + char(39) +', ' + char(39) + 'OK' + char(39)

* Noel

nns80 Mr or Mrs. 500 Points: 524 More actions · Answer 7

Thanks for your reply. I think the solution to use the temp table will work for me as i have n status values to check and i will have to run a loop on it to add to the temp table and then check it thru the temp table.

Thanks,

-NS.