title: How to get the return value and rows in c# thru stored procedure
body: <p>In the below Stored procedure, i am returning a row if all the conditions satisfies, orelse i am returning a message like which condition is nor satisfied.
  The Stored proc is working perfectly...</p>
  
  <pre><code>ALTER PROCEDURE dbo.BookingCheck
      (
      @int_duration_of_stay   int ,
      @int_number_of_guests   int,
      @date_of_application    date,
      @date_of_checkin        date,
      @date_of_checkout       date,
      @str_room_type          varchar(50),
      @ret_value              varchar(100) = '' output
      )
  AS
      DECLARE @MaxPer int
      DECLARE @BasicCharge int
      DECLARE @SurCharge int
      DECLARE @TotalAmount int
      DECLARE @NoOfDays int
      DECLARE @Free VARCHAR(10)
  
      IF @int_duration_of_stay &gt; 6
      BEGIN
          SET @NoOfDays = @int_duration_of_stay
          SET @Free = 'Yes'
      END 
      ELSE
      BEGIN
          SET @NoOfDays = @int_duration_of_stay - 1
          SET @Free = 'No'
      END
  
  
      SELECT @MaxPer = int_max_pax, @BasicCharge = flt_basic_charge, @SurCharge = flt_surcharge_per_pax 
      FROM RoomTypes WHERE UPPER(str_room_type) = UPPER(@str_room_type)
  
  IF DATEDIFF(DAY, GETDATE(), @date_of_checkin) &lt; 40
  BEGIN
      IF @int_number_of_guests &lt;= @MaxPer
      BEGIN
          SET @TotalAmount = (@NoOfDays * @int_number_of_guests * @SurCharge) + @BasicCharge
          SET @ret_value = 'Success'
  
          SELECT @str_room_type as 'Room Type', @MaxPer as 'Max Persons Allowed', @int_number_of_guests as 'No. of persons requested',  
          @int_duration_of_stay as 'No. of days stay', @BasicCharge as 'Basic Charge',  @SurCharge as 'Sur Charge', @Free as 'Complimentary',
           @TotalAmount as 'Total Amount'
      END
      ELSE
      BEGIN
          SET @ret_value = 'Max persons allowed is ' + CONVERT(VARCHAR(20), @MaxPer)
      END
  END
  ELSE
  BEGIN
      SET @ret_value = 'The check in date should be less than 40 days from current date.'
  END
  RETURN
  </code></pre>
  
  <hr>
  
  <p>The problem is dont know how to get the return message or return row from the SP using c#.</p>
  
  <p>The below code returns me the rows if the condition is satisfied in SP. if not, i am not getting the return message. How to get that ?</p>
  
  <pre><code> public DataSet BookingCheck(int duration_of_stay, int number_of_guests,
      string date_of_application, string date_of_checkin, string date_of_checkout,
      string room_type)
  {
      DataSet dsGetBookingCheck = new DataSet();
      SqlConnection conn = new SqlConnection(Con);
      SqlCommand command = new SqlCommand("BookingCheck", conn);
      command.CommandType = CommandType.StoredProcedure;
      SqlDataAdapter da = new SqlDataAdapter();
      SqlParameter param = new SqlParameter();
      param = command.Parameters.Add("@int_duration_of_stay", SqlDbType.Int);
      param.Value = duration_of_stay;
      param = command.Parameters.Add("@int_number_of_guests", SqlDbType.Int);
      param.Value = number_of_guests;
      param = command.Parameters.Add("@date_of_application", SqlDbType.Date);
      param.Value = date_of_application;
      param = command.Parameters.Add("@date_of_checkin", SqlDbType.Date);
      param.Value = date_of_checkin;
      param = command.Parameters.Add("@date_of_checkout", SqlDbType.Date);
      param.Value = date_of_checkout;
      param = command.Parameters.Add("@str_room_type", SqlDbType.VarChar, 50);
      param.Value = room_type;
      conn.Open();
      command.ExecuteNonQuery();
      da.SelectCommand = command;
      da.Fill(dsGetBookingCheck);
      conn.Close();
      return dsGetBookingCheck;
  }
  </code></pre>