Stored procedure runs correctly but DataReader gets wrong result...sometimes

Question

I have a stored procedure for a travel booking system that takes in a TripID and identifies whether the trip is domestic or not (e.g. whether the origin and destination countries are the same for all trip legs). When I run the procedure from SSMS, it correctly returns 1 for domestic and 0 for international. However, when I try to access the data in my application through DataReader, it inappropriately returns 0 for domestic trips.

That being said, I don't think the problem lies purely with the DataReader because when I alter my stored procedure to return 1 immediately, DataReader will correctly detect this value.

Can anyone suggest changes to my code to fix this behavior?

Here is the stored procedure, pared down:

SET NOCOUNT ON;

    --  EXEC CheckIsDomestic 6343

    Declare @HomeOffice INT = (SELECT TOP 1 o.DestinationID
                                    FROM  TR_Trips t
                                    JOIN TR_Travelers ta ON t.TravelerID = ta.TravelerID
                                    JOIN TR_OfficeLocations o ON ta.OfficeID = o.Office_Loc_Id
                                    WHERE t.TripID = @TripID)

    SELECT l.Destination_ID AS DestinationID
    INTO #TempDest
    FROM TR_Trips t JOIN TR_Legs l ON t.TripID = l.TripID
    WHERE t.TripID = @TripID                                                    

    --Check whether there is a destination in the list that is different than the home country                                                      
    DECLARE @CountRows int = (SELECT COUNT(*) 
    FROM #TempDest t
    WHERE DestinationID <> @HomeOffice )                                                        

    IF @CountRows > 0
    BEGIN 
    SELECT 0
    RETURN --tried with and without RETURN; no change
    END
    ELSE 
    BEGIN SELECT 1
    RETURN
    END

And here are the applicable parts of my application:

 public bool IsDomestic(int TripID)
        {
            bool ReturnValue = true;
            NewStoredProcedureCommand("CheckIsDomestic");
            AddParameter("@TripID", TripID, System.Data.SqlDbType.Bit);
             ReturnValue = Execute_ReturnValueBool();
            return ReturnValue;
        }

 public Boolean Execute_ReturnValueBool()
        {
            if (sqlCommand == null)
                NewCommand();
                if (sqlCommand.Connection.State != ConnectionState.Open)
                    sqlCommand.Connection.Open();
                bool ReturnValue = false;
                SqlDataReader DR = sqlCommand.ExecuteReader();
                if (DR.HasRows)
                {
                    DR.Read();
                    System.Diagnostics.Debug.WriteLine(DR[0]);
                    ReturnValue = Convert.ToBoolean(DR[0]);
                }
                DR.Close();
                sqlCommand.Connection.Close();
                return ReturnValue;
        }

Answer 1

Why are you using the BIT type for your TripID parameter in the application code? Try setting it to INT.