CREATE TABLE [Paises] (
	[Pais] [smallint] NOT NULL ,
	[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UltimaAct] [timestamp] NULL ,
	CONSTRAINT [PK_Paises] PRIMARY KEY  CLUSTERED 
	(
		[Pais]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [Entidades] (
	[Entidad] [int] NOT NULL ,
	[Pais] [smallint] NOT NULL ,
	[Descripcion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UltimaAct] [timestamp] NOT NULL ,
	CONSTRAINT [PK_Entidades] PRIMARY KEY  CLUSTERED 
	(
		[Entidad]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





CREATE  proc dbo.Paises_Delete @Pais smallint
as
begin

begin tran

delete Paises where Pais=@Pais

if @@ERROR <> 0 begin
	rollback tran
	raiserror('Error al eliminar el registro en la tabla Paises', 16, 1)
	return
end
commit tran

end 




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  proc dbo.Paises_Save
		@Pais smallint OUTPUT,
		@Descripcion varchar(50),
		@UltimaAct int OUTPUT
as
begin
declare @updateType varchar(100)

begin tran

if exists (select '' from dbo.Paises where Pais=@Pais) begin
	-- Existe el registro y se actualizará...
	set @updateType='actualizar'

	if cast((select UltimaAct from dbo.Paises (nolock) where Pais=@Pais) as int) <> @UltimaAct begin
		rollback tran
		raiserror('MSG #5001. El registro fué actualizado desde otra locación, no se realizó ninguna actualización.', 16, 1)
		return
	end

	update Paises set 
		Descripcion = @Descripcion
	where Pais=@Pais

end else begin
	-- Es un registro nuevo...
	set @updateType='insertar'

	insert Paises (
		Pais, 
		Descripcion
		)
	values (
		@Pais,
		@Descripcion
		)

end

set @UltimaAct = cast((select ultimaAct from Paises (nolock) where Pais=@Pais) as int)

if @@ERROR <> 0 begin
	rollback tran
	raiserror('Error al %s en la tabla Paises', 16, 1, @updateType)
	return
end
commit tran

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




ALTER   proc dbo.Paises_Select 
@Pais smallint = null
as
begin
	select 
		Pais,
		Descripcion,
		CAST (UltimaAct as INT) as UltimaAct
	from Paises (nolock)
	where (@Pais is null Or Pais=@Pais)
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  proc dbo.Entidad_Delete @Entidad int
as
begin

begin tran

delete entidades where Entidad=@Entidad

if @@ERROR <> 0 begin
	rollback tran
	raiserror('Error al eliminar el registro en la tabla entidades', 16, 1)
	return
end
commit tran

end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  proc dbo.Entidad_Save
		@Entidad int OUTPUT,
		@Pais smallint,
		@Descripcion varchar(255),
		@UltimaAct int OUTPUT
as
begin
declare @updateType varchar(100)

begin tran

if exists (select * from dbo.entidades where Entidad=@Entidad) begin
	-- Existe el registro y se actualizará...
	set @updateType='actualizar'

	if cast((select UltimaAct from dbo.entidades (nolock) where Entidad=@Entidad) as int) <> @UltimaAct begin
		rollback tran
		raiserror('[MSG #5001]. El registro fué actualizado desde otra locación, no se realizó ninguna actualización.', 16, 1)
		return
	end

	update entidades set 
		Pais = @Pais,
		Descripcion = @Descripcion
	where Entidad=@Entidad

end else begin
	-- Es un registro nuevo...
	set @updateType='insertar'

	insert entidades (
		Entidad, 
		Pais, 
		Descripcion
		)
	values (
		@Entidad,
		@Pais,
		@Descripcion
		)

end

set @UltimaAct = cast((select ultimaAct from entidades (nolock) where Entidad=@Entidad) as int)

if @@ERROR <> 0 begin
	rollback tran
	raiserror('Error al %s en la tabla entidades', 16, 1, @updateType)
	return
end
commit tran

end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  proc dbo.Entidad_Select @Entidad int = null
as
begin
	select 
		Entidad,
		Pais,
		Descripcion,
		CAST( UltimaAct as INT) as UltimaAct
	from entidades (nolock)
	where (Entidad=@Entidad or @Entidad is null)
end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO