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
}