Problem with SP

[PatrickIRL]

Hi, I have a stored procedure (sql server) with 3 parameters that runs fine in query analyzer but returns nothing when I run it through my web page.

ALTER PROCEDURE [dbo].[PROCEDURENAME]
	@stateschoolID varchar(100),
	@gender int,
	@grades varchar(100)
AS
	SELECT 
		*
	FROM
		<TABLE>
	WHERE
		stateSchoolID_c = @stateschoolID AND
		gender_n = @gender AND
		gradeID_c IN (@grades)

I think the problem may be the way I'm using the IN statement for the grades. It can be a list so I could pass in a value such as '01','04'

This is how I call it...

using (IDbCommand cmd = cn.CreateCommand())
{
	cmd.CommandText = "PROCEDURENAME";
      cmd.CommandType = CommandType.StoredProcedure;
      //
      IDbDataParameter pmStateSchoolID = cmd.CreateParameter();
      pmStateSchoolID.ParameterName = "@stateSchoolID";
      pmStateSchoolID.Value = _stateSchoolID;
      cmd.Parameters.Add(pmStateSchoolID);
      //
      IDbDataParameter pmGender = cmd.CreateParameter();
      pmGender.ParameterName = "@gender";
      pmGender.Value = Convert.ToInt16(gender);
      cmd.Parameters.Add(pmGender);
      //
      IDbDataParameter pmGrades = cmd.CreateParameter();
      pmGrades.ParameterName = "@grades";
      pmGrades.Value = grade;
      cmd.Parameters.Add(pmGrades);
      //
      using (IDataReader dr = cmd.ExecuteReader())
      {
      	while (dr.Read())
            {
		}
	}
}

Any ideas ????

Thanks in advance, Patrick

 

[jbenson001]

Any ideas ????

No idea. You didn't post the code that generates the value for the grades parameter.

 

[PatrickIRL]

Never mind, I looked at faq183-5207 and solved it using the function created by donutman.

Patrick