SQL SERVER 2008 IN

Question

Somebody please me what I am doing wrong,

Basically I am calling a stored procedure in code, in my where clause I using an IN function. I am building a list of items can some tell me the correct way to do this in code, the procedure works fine in management studio, but for some reason when i call it in code, its not actiing right , it got something to do with the list I am building. review details below. I am trying to basically build a list, the list can contain up to 900 values max base upon our data, keep that in mind



                          using (SqlConnection c = new SqlConnection(ColorClient.Properties.Settings.Default.ColorConnectionString))
                         {
                            c.Open();
                            using (SqlCommand cm = c.CreateCommand())
                            {
                                cm.CommandType = CommandType.StoredProcedure;
                                cm.CommandText = "[dbo].[prc_GetColors]";
                                if (string.IsNullOrEmpty(ColorCodeList))
                                {
                                    cm.Parameters.AddWithValue("@ColorCodeList", DBNull.Value);
                                }
                                else
                                {
                                    //I hard coded this to make an example of an list I am trying to build to send to the stored procedure
                                    ColorCodeList= "'Red','Blue'";
                                    cm.Parameters.AddWithValue("@ColorCodeList", ColorCodeList);
                                }

                                using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                                {
                                    while (dr.Read())
                                    {
                                            //Its not getting in here.
                                     }

                             }


Review procedure below:

ALTER PROCEDURE [dbo].[prc_GetColors]
(
    @ColorCodeList varchar(MAX) = NULL,

)

AS


SELECT   ColorTypes, Colors
 
FROM ColorTable

WHERE ColorCode IN (@ColorCodeList)



RETURN



                       }