[C#/SQL] Read Multiple values from stored procedure

If you want to return multiple values from a single column then your stored procedure is wrong. You should not be using OUTPUT since it will only return a single value.

CREATE PROCEDURE _Proc(@id int)
AS
Begin
   SELECT EventID from Table where ID = @id
End

You will then use a SqlDataReader to read the values returned by the stored procedure. This examples just adds the returned eventIds to a list.

int id = ????;  // Whatever value you are going to passed into the stored procedure
List<int> eventIds = new List<int>();

using (SqlConnection conn = new SqlConnection())
{
    SqlCommand cmd = new SqlCommand("_proc", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@ID", id));
 
    conn.Open();
 
    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
	using (SqlDataReader reader = command.ExecuteReader();)
	{
	    while (reader.Read())
		eventIds.Add(int.Parse(reader["EventId"].ToString()));
	}
    }
}
</int></int>

You will be able to access the value of output parameter after closing the reader only.

Create your parameters with INOUT keyword inside your proc, while adding the parameter to command object set the appropriate property of the parameter object, set values to your @events variable inside your procedure.

now you will be able to access as are expecting.

you can use standart ways that you use before in normal queries
but in Sql command you must write EXEC before your store procedure name
and dont use commandtype


like this :

SqlConnection con = new SqlConnection(["ConnectionString"])
SqlCommand com = new SqlCommand("EXEC _Proc @id",con);
com.Parameters.AddWithValue("@id",["IDVALUE"]);
con.Open();
SqlDataReader rdr = com.ExecuteReader();
ArrayList liste = new ArrayList();
While(rdr.Read())
{
liste.Add(rdr[0]); //if it returns multiple you can add them another arrays=> liste1.Add(rdr[1]) ..
}
con.Close();