Passing a string to SQL as a list of as ('Value1','Value2', etc) and use the IN operator

Question

I'm passing in a list as a string to a stored proc of format "('Value1', 'Value2', 'Value3')" and storing it in @Type.

For the life of me, I can't figure out how to say something like

Select * from whatever where colName in @Type where @Type is that formatted list.

I know this is going to be simple enough that I'll facepalm but "How do?"

This is probably the second most asked question after 'how do I turn columns into rows'. Here's a multitude of solutions that I found after a quick search. http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause. If you need help implementing any of those please post back. — Nick.Mc, Oct 27 '14 at 12:35
I didn't notice that you had the brackets as part of the varialble. Even if it you get this working worked with something like 'fred', 'barney', 'wilma', you would still need to do something to escape apostrophes. — Dan Bracuk, Oct 27 '14 at 12:42
Yeah, sql isn't my strongest suit. I've edited my question to include the actual code I'm trying to modify with @DanBracuk's suggestion included — user4593252, Oct 27 '14 at 12:47
@Nick.McDermaid: I'm not the strongest at SQL so I'm not sure any of the solutions in the link you posted actually does what I need it to. — user4593252, Oct 27 '14 at 12:49
http://sqlperformance.com/split & http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql — Aaron Bertrand, Oct 27 '14 at 13:18
@AaronBertrand: You understood *exactly* what I was trying to say even though I didn't know how to word it correctly. I'm going through that now and I have control over how the list gets passed to the proc. If I can get this to work using what you posted, please post as an answer and I'll accept it as the answer to my problem. Thank you. — user4593252, Oct 27 '14 at 13:29
@AaronBertrand: Your suggestions have worked. If you want to repost your suggestion as an answer, I'll mark is as the accepted answer and explain why it's correct to anyone else that comes behind... — user4593252, Oct 27 '14 at 17:18

score 1 · Answer 1 · answered Oct 27 '14 at 12:42

Not sure if this helps at all but what if you use a table type variable like

DECLARE @MyTable table(
    col1 varchar(10) NOT NULL);

INSERT INTO @MyTable(col1)
select 'Value1'
union
select 'Value2'
union
select 'Value3';

Then fetch data

Select * from whatever where colName in (select col1 from @MyTable)

Passing a string to SQL as a list of as ('Value1','Value2', etc) and use the IN operator

1 Answers1