ALTER PROCEDURE Insertar_Cine(@nombre as varchar(50))
AS
begin
insert into Cine(Nombre) values(@nombre)
end
ALTER PROCEDURE Insertar_Sala(@cine as int,@tipo as varchar(50),@cant_asientos as int)
AS
declare @error as int
BEGIN
BEGIN TRY
if not exists(SELECT ID_Cine from Cine
WHERE ID_Cine = @cine)
begin
print 'No existe la sala'
return -100002
end
else
BEGIN TRANSACTION
insert into Sala(ID_Cine,Tipo, Asientos) values(@cine,@tipo , @cant_asientos)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @error=@@error
if(@error > 0)
ROLLBACK TRANSACTION
return @error * -1
END CATCH
return 1
END
ALTER PROCEDURE Insertar_Tiquete(@tanda as int,@cliente as int,@sala as int,@asiento as int ,@pelicula as int,@fecha as datetime ,@pago as varchar)
AS
declare @error as int
BEGIN
--Validaciones
BEGIN TRY
if not exists(SELECT ID_Tanda from Tanda
WHERE ID_Tanda = @tanda)
begin
print 'No existe la tanda'
return -100001
end
else
if not exists(SELECT ID_Sala from Sala
WHERE ID_Sala = @sala)
begin
print 'No existe la sala'
return -100002
end
else
if not exists(SELECT ID_Cliente from Cliente
WHERE ID_Cliente = @cliente)
begin
print 'No existe cliente'
return -100003
end
else
if not exists(SELECT ID_Pelicula from Pelicula
WHERE ID_Pelicula = @pelicula)
begin
print 'No existe la pelicula'
return -100004
end
else
if ((SELECT Asientos from Sala
WHERE ID_Sala = @sala) < @asiento)
begin
print 'El asiento no existe'
return -100005
end
else
if exists(SELECT ID_Asiento from Asiento
WHERE ID_Asiento = @asiento and ID_Tanda = @tanda)
begin
print 'Asiento ocupado'
return -100005
end
else
--Insertamos el asiento en la lista de asientos ocupados y el tiquete
BEGIN TRANSACTION
insert into Tiquete(ID_Tanda,ID_Cliente,ID_Sala,ID_Asiento,ID_Pelicula,Fecha,TipoPago) values(@tanda, @cliente, @sala,@asiento,@pelicula,@fecha,@pago)
insert into Asiento(ID_Asiento,ID_Tanda, Estado) values (@asiento, @tanda , 3)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @error=@@error
if(@error > 0)
ROLLBACK TRANSACTION
return @error * -1
END CATCH
return 1
END
ALTER PROCEDURE Insertar_Tanda( @sala as int, @pelicula as int,@fecha as datetime, @inicio as datetime, @fin as datetime )
AS
declare @error as int
BEGIN
BEGIN TRY
if not exists(SELECT ID_Sala from Sala
WHERE ID_Sala = @sala)
begin
print 'No existe la sala'
return -100001
end
else
if not exists(SELECT ID_Pelicula from Pelicula
WHERE ID_Pelicula = @pelicula)
begin
print 'No existe la pelicula'
return -100004
end
else
BEGIN TRANSACTION
insert into Tanda(ID_Sala,ID_Pelicula,Fecha,Inicio,Fin) values( @sala, @pelicula,@fecha,@inicio,@fin)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @error=@@error
if(@error > 0)
ROLLBACK TRANSACTION
return @error * -1
END CATCH
END
ALTER PROCEDURE SPcartelera
AS
BEGIN
SELECT tanda.ID_Sala 'Sala',
CONVERT(VARCHAR(100),tanda.fecha, 101) 'Fecha',
CONVERT(VARCHAR(100),tanda.inicio, 108) 'Hora de inicio',
CONVERT(VARCHAR(100),tanda.fin, 108) 'Hora de finalización',
pelicula.nombre
FROM tanda
INNER JOIN pelicula ON tanda.ID_Pelicula = pelicula.ID_Pelicula
END
+Duración: 3 horas aprox.
