sql procedure returns only the last row instead of multiple rows

I have verified that the procedure does return multiple ro The problem is that my sql procedure returns only one row, but from asp side I can only get one:

ALTER PROCEDURE cphsdbm.tp_Trip_List
@UserID int,
@Trip_Name varchar(50) output
AS
BEGIN
SELECT @Trip_Name = Trip_Name FROM tbl_Trip WHER开发者_StackOverflow中文版E UserID=@UserID; 
END

My code:

do
{
while (rdr2.Read())
{
trip_count++;
Response.Write("Success!");
}
trip_count++;
} while (rdr2.NextResult());

---

To return multiple rows, your stored procedure should return a result set (rather than a single output value), i.e.:

ALTER PROCEDURE cphsdbm.tp_Trip_List 
@UserID int
AS 
BEGIN 
     SELECT Trip_Name FROM tbl_Trip WHERE UserID = @UserID;  
END 

To read:

while (rdr2.Read()) 
{ 
   trip_count++; 

   // ... do something with trip name...
} 

---

The stored procedure you are using should not return even one row. It will only return Trip_Name value in output variable you are passing to the stored procedure. I don't know the structure of your database or what you need to read from table so I cannot suggest anything further unless you elaborate on your requirement.

Moreover there will not be any NextResult() in the reader.