Sql Procedimento

Question

Good afternoon, can anyone help me create a procedure in the SQL database ? To make a reservation for a room, I have to select a room and declare the Date Entry and Date.

What I want, is to make a reservation in the room 1 between the days 13/06/2018 and 14/06/2018, and then if someone wants to make another reservation in that same room between the days 13/06/2018 and 14/06/2018, given that this room already has a reservation that day, I want a busy message to appear. Someone can help me?

Reserve table

 public partial class Reserva
    {
        public int ID_Reserva { get; set; }
        public int ID_Cliente { get; set; }
        public int ID_Quarto { get; set; }
        public System.DateTime DataEntrada { get; set; }
        public Nullable<System.DateTime> DataSaida { get; set; }
        public int NumeroPessoas { get; set; }
        public Nullable<int> NumeroNoites { get; set; }
        public Nullable<decimal> Preço { get; set; }
        public string Observaçoes { get; set; }

        public virtual Cliente Cliente { get; set; }
        public virtual Quarto Quarto { get; set; }
    }

Quarto Table

public partial class Quarto
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Quarto()
    {
        this.Reserva = new HashSet<Reserva>();
    }

    public int ID_Quarto { get; set; }
    public string TipoQuarto { get; set; }
    public string EstadoQuarto { get; set; }
    public Nullable<decimal> PreçoQuarto { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Reserva> Reserva { get; set; }
}

Answer 1

You can use a procedure as shown below:

CREATE PROCEDURE reservar(@id_quarto    INT,
                          @data_entrada DATE,
                          @data_saida   DATE)
AS
BEGIN
  DECLARE @dias TABLE(dia DATE);

  -- Gera um registro para cada dia dentro do período de datas escolhido
  WITH dias AS (
    SELECT @data_entrada AS dia
     UNION ALL
    SELECT DATEADD(DAY, 1, d.dia)
      FROM dias d
     WHERE d.dia < @data_saida
  )
  INSERT INTO @dias(dia)
  SELECT d.dia
    FROM dias d;

  -- Verifica se existe uma reserva para algum dos dias que foi informado
  IF EXISTS(SELECT 1
              FROM reserva r
             INNER JOIN @dias d ON d.dia BETWEEN r.data_entrada AND r.data_saida
             WHERE r.id_quarto = @id_quarto)
  BEGIN
    RAISERROR('Já existe uma reserva para a data informada no quarto desejado', 16, 1);
    RETURN;
  END;

  INSERT INTO reserva(id_quarto, data_entrada, data_saida)
               VALUES(@id_quarto, @data_entrada, @data_saida);
END;
GO

In the above procedure we generate a list of days within the reported interval and seek a reservation that conflicts with the date and room chosen. If there is no conflict in these terms, a record is inserted in the table reserva.