0

I'm trying to use a multi valued string in a Store Procedure, but the IN operator is only accepting the values in Hard Coded mode.

Like, if I write the values directly in the Code it works, but if the value is retrieved from the SELECT command, it return nothing...

Anyone can help on this?

For testing:

DECLARE @AUX nvarchar(255);

SET @AUX = (SELECT GROUP FROM FRUITS WHERE FRUIT = 'APPLES');

PRINT @AUX;

SELECT * 
FROM FRUITS 
WHERE FRUIT IN (@AUX);

This returns nothing. But the value inside @AUX is retrieved, and contains something like 'Red','Green','Starkling' .

If I use that Select command only it works.

And if I hard core inside the IN the Values it works also.

SELECT * 
FROM FRUITS 
WHERE FRUIT IN ('Red', 'Green', 'Starkling');

This works.

I tried to output the value inside the variable @AUX and is correct... So why the IN doesn't read the values?

TIA

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
chimino
  • 29
  • 1
  • 4
  • If inside the SELECT there's only one Value like 'Red', the function works... only with multi value like 'Red,'Green,'etc' , it doesn't work – chimino Dec 17 '15 at 17:21
  • 1
    Your query doesn't make sense. Your selecting GROUP from the FRUITS table and then using the values of GROUP as parameters for the FRUIT field. – Robbert Dec 17 '15 at 17:25
  • The Table Fruits Has: ID FRUIT GROUP It can have values like: (1 ; 'Orange' ; 'Big') Or for multi (2 ; 'Apple' ; 'Red','Green,'Starkling' ) – chimino Dec 17 '15 at 17:27
  • 1
    You're assuming your code get's translated to `WHERE FRUIT IN ('Red', 'Green', 'Starkling')`, but that's wrong. @AUX is a single string. Really, what the database engine is seeing is this `WHERE FRUIT IN ('''Red'', ''Green'', ''Starkling''')`. It doesn't see them as separate values of 3 different strings. It sees it as one value of one string that happens to contain commas and single quotes in that singular string. – Rabbit Dec 17 '15 at 17:30
  • 1
    Additional reading: http://sqlstudies.com/2013/04/08/how-do-i-use-a-variable-in-an-in-clause/ – AHiggins Dec 17 '15 at 17:30
  • 1
    ohhhh so it must be that man! thanks, I'll try to read about it! – chimino Dec 17 '15 at 17:34
  • The best way to fix this issue is to change your stored procedure to accept a table (of friuts) and then join to that table – Hogan Dec 17 '15 at 17:36

2 Answers2

2

If the Group field contains a string list of single quoted values... then you would have to do some kind of dynamic sql to insert that string into the query. As it stands it is looking for a fruit called "'Red','Green','Starkling'" instead of one of them from the list.

To do dynamic sql it would look like:

exec(
'
SELECT * 
FROM FRUITS 
WHERE FRUIT IN (' + @AUX + ');'
)
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • Thkx! But it only returns the value of the first value in the group. For ex: if inside AUX is 'Red','Green','Starkling', is only returning info about the first one 'Red' (instead of returning values of all of them). hard to go around this :\ – chimino Dec 17 '15 at 18:43
0

Why not skip the variable assignment and just do this:

SELECT * 
FROM FRUITS 
WHERE FRUIT IN (SELECT GROUP FROM FRUITS WHERE FRUIT = 'APPLES');

For what it's worth, I'm going with the column names you specified above, but they don't seem to match (group values may not match fruit values). What you may actually want is:

SELECT * 
FROM FRUITS 
WHERE GROUP IN (SELECT GROUP FROM FRUITS WHERE FRUIT = 'APPLES');

Or maybe:

SELECT * 
FROM FRUITS 
WHERE FRUIT IN (SELECT FRUIT FROM FRUITS WHERE GROUP = 'APPLES');
morgb
  • 2,252
  • 2
  • 14
  • 14
  • Yes I tried with the SELECT directly inside the IN , but it does not work... only if I hard code the values. But if the SELECT returns only one value, it works, doest not work only with multi values, can't understand why... :s – chimino Dec 17 '15 at 17:33