Sql Procedure [closed]

Question

Sql Procedure [closed]

Navigation

#1 by (0 votes)

2

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

What I want is if you make a reservation in room 1 between June 13th, 20th and June 14th, and then if someone wants to make another reservation in that room between June 13th, 06/14/2018, since this room already has a reservation on that day, I want a message to appear busy. Can someone help me?

Reservation 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; }
    }

Room 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; }
}

sql

asked by anonymous 13.06.2018 / 14:08

1 answer

React Router: Use more files with routes How to use junit to test jax rs on wildfly

score 0 · Answer 1

You can use a procedure like the one exemplified 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 informed interval and look for a reservation that conflicts with the date and room chosen. If no conflict occurs in these terms, a record is inserted into the reserva table.