use master
go
drop database taller
go
Create database taller
GO

USE [taller]
GO

/****** Object:  Table [dbo].[Coche]    Script Date: 04/30/2013 09:59:37 ******/

CREATE TABLE persona (
		idPersona INT NOT NULL primary key,
		nif CHAR(10) NOT NULL,
		NombreRazonSocial CHAR(50) NOT NULL,
		apellido1 CHAR(50) NULL,
		apellido2 CHAR(50) NULL,
)
go
insert into persona values (1,'12000000S','Antonio','Salgado','Fernandez')
insert into persona values (2,'12000001S','Jose Antonio','Alonso','Fernandez')
insert into persona values (3,'12000002S','Pedro','Gutierrez','Lopez')
insert into persona values (4,'12000003S','Victor','Valencia','Salto')
insert into persona values (5,'12000004S','Juan','Salto','Garcia')
go

CREATE TABLE Coche(
	id_coche [int] IDENTITY(1,1) primary key NOT NULL,
	[marca] [varchar](20) NULL,
	[matricula] [varchar](20) NULL unique,
	[fechaMatricula] [date] NULL,
	[idPropietario] [int] NULL,
    foreign key(idPropietario) references persona(idPersona)  
)
go

set dateformat 'YMD'
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Renault','7634BYK','2003/12/31', 1)
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Opel','7645FGG','2004/11/01', 2)
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Renault','5612BRD','2007/03/11', 3)
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Seat','1010ASD','2008/04/30', 4)
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Audi','2222SSD','2004/11/11', 5)
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Audi','4535DFD','2010/11/17', 2)
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Citroen','9934BRT','2002/05/30', 1)
insert into Coche (marca,matricula,fechaMatricula,idPropietario) values ('Renault','4521BYC','2007/11/22', 3)
go


create procedure ventaCoche @idCoche int,
							@idComprador int, 
                            @salida int OUTPUT 
as begin 
   begin try
   
     DECLARE @TranCounter INT;
     SET @TranCounter = @@TRANCOUNT;
     IF @TranCounter > 0
       SAVE TRANSACTION ProcedureSave;
     else 
        begin transaction
     update coche set idPropietario = @idComprador 
        where id_coche = @idCoche
     if @TranCounter = 0 
       commit transaction
     set @salida = 0
   end try
   begin catch
      IF @TranCounter = 0 begin
            ROLLBACK TRANSACTION;
       end
       ELSE begin
            IF XACT_STATE() = -1 begin
                ROLLBACK TRANSACTION ProcedureSave;
            end
        end
        set @salida = -1
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE();
        SELECT @ErrorSeverity = ERROR_SEVERITY();
        SELECT @ErrorState = ERROR_STATE();
        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
   end catch
end


end