Dealing with delimited lists (Usually separated by a comma) in SQL is a problem easily handled by a simple function and a Tally Table.  (Tally tables are also often referred to as Numbers tables or spt_values tables.  If you still don't know what that is, please see this excellent article on Tally tables written by my friend and SSC heavyweight Jeff Moden.)  This particular implementation is somewhat specific in nature but can give you an alternative to Dynamic SQL when you want to pass in a list as a parameter and do an IN in a Stored Procedure. The following function will take your delimiter and string and parse it into a table so you can do your IN.  (I'm leaving my standard header on the function in this case because there are some good notes in there.)

/*
=============================================================================================
CREATE DATE:     02/27/2010
LAST MODIFIED:    02/27/2010
CREATED BY:        SETH PHELABAUM
PURPOSE:        Splits a string based on a passed in delimiter and returns a table.
ISSUES:            Strings with extra 's will break this function, handle that on the end that calls it.
Notes:            To make it a simpler function, I removed the peice that trimmed spaces around commas.  Do
                this before or after calling it.
Revision History:
Date     By        Change Made
-------- ---      -------------------------------------
=============================================================================================
GRANT SELECT ON TVF_TallySplit TO [Somebody]
SELECT * FROM TVF_TallySplit(',','Orange,Apple,Banana,Pear,Watermelon,Grape')
SELECT * FROM TVF_TallySplit('*','Orange*Apple*Banana*Pear*Watermelon*Grape')
DROP FUNCTION TVF_TallySplit
*/

CREATE FUNCTION TVF_TallySplit(
    @Delim            CHAR(1),            -- List Delimiter
    @String            VARCHAR(8000))
RETURNS TABLE
AS

RETURN(
    SELECT SUBSTRING(@Delim + @String + @Delim,N+1,CHARINDEX(@Delim,@Delim + @String + @Delim,N+1)-N-1) ListValue
    FROM Tally
    WHERE N < LEN(@Delim + @String + @Delim)
    AND SUBSTRING(@Delim + @String + @Delim,N,1) = @Delim )

 

What to do with this

Let's say you have a table containing names of your favorite fruits.  (In case you were wondering... No, these aren't my favorite fruits; they were just ones that immediately came to mind when writing this.  I don't even like half of these.) 

CREATE TABLE Fruits(
Name        varchar(25))

INSERT INTO Fruits(Name)
SELECT 'Apple' UNION ALL SELECT 'Banana' UNION ALL SELECT 'Grapefruit' UNION ALL
SELECT 'Kiwi' UNION ALL SELECT'Tomatoe' UNION ALL SELECT 'Grape' UNION ALL
SELECT 'Orange' UNION ALL SELECT 'DragonFruit' UNION ALL SELECT 'Strawberry' 

You then ask someone else what their favorite fruits are and want to see what fruits you have in common.  You might think you could just write a query for that like this:

DECLARE @YFFruits varchar(200)
SET @YFFruits = 'Orange,Apple,Banana,Pear,Watermelon,Grape'
-- OR SET @YFFruits = '''Orange'',''Apple'',''Banana'',''Pear'',''Watermelon'',''Grape'''

SELECT * FROM Fruits where Name LIKE (@YFFruits)
-- OR SELECT * FROM Fruits where Name IN (@YFFruits) 

However, this won't work because in all these cases SQL is looking for a single fruit named : 'Orange,Apple,Banana,Pear,Watermelon,Grape' not any fruit in what is really a list.

A common solution for this is to use Dynamic SQL which would make your query this:

DECLARE @YFFruits varchar(200)
SET @YFFruits = '''Orange'',''Apple'',''Banana'',''Pear'',''Watermelon'',''Grape'''
EXEC('SELECT * FROM Fruits WHERE Name IN (' + @YFFruits + ')') 

This works and will properly match up your fruits.

The above function allows you to accomplish your goal without Dynamic SQL with a query that looks like this:

DECLARE @YFFruits varchar(200)
SET @YFFruits = 'Orange,Apple,Banana,Pear,Watermelon,Grape'
SELECT * FROM Fruits where Name IN (SELECT * FROM Util.dbo.TVF_TallySplit(',',@YFFruits)) 

You can also simply join to the table instead of using the IN keyword which gives you more flexibility in your query writing.

DECLARE @YFFruits varchar(200)
SET @YFFruits = 'Orange,Apple,Banana,Pear,Watermelon,Grape'
SELECT Fruits.*
FROM Fruits
    INNER JOIN Util.dbo.TVF_TallySplit(',',@YFFruits)) T ON Fruits.Name = T.ListValue 

Note that because I wanted to keep the function somewhat simple, it does not handle extra spaces around the commas.  Single quotes within the string will also break it which limits its usage somewhat.  If this is a concern for your implementation, you either need to replace the single quotes on both sides or use a different method.  Despite the fact that the example above uses a list of strings, in real life situations I use this mainly for lists of uniqueidentifiers or numbers where single quotes/spaces are never an issue.