/*
drop Table UserDetails
drop Table UserRole
drop procedure DNS_INSUserDetails
drop procedure DNS_UPDUserDetails
drop Type TVPUserDetails
*/

Create Table UserDetails
(
UserID int identity(1,1) primary key,
UserName varchar(50),
FirstName Varchar(50),
LastName Varchar(50),
Email Varchar(100), 
IsActive bit)

Create Table UserRole
(UserRolePK int identity(1,1),
 UserID int FOREIGN KEY REFERENCES UserDetails(UserID), 
UserRole varchar(30),
BeginDate datetime, TermDate datetime)


Create Type TVPUserDetails as table
(
UserID int,
UserName varchar(50),
FirstName Varchar(50),
LastName Varchar(50),
Email Varchar(100), 
UserRole Varchar(30),
IsActive bit )

--Insert 
Create procedure DNS_INSUserDetails
(
@tvpUserDetails TVPUserDetails  readonly
)
as
Begin 

declare @UserID int
	If not exists( Select UD.UserName  from UserDetails UD,  @tvpUserDetails TVPUD 
		       where UD.UserName = TVPUD.UserName)
	Begin 
		Insert UserDetails (UserName, FirstName, LastName, Email, IsActive)
		Select UserName, FirstName, LastName, Email, IsActive from @tvpUserDetails

		set @UserID  = @@IDENTITY

		Insert UserRole(UserID, UserRole, BeginDate, TermDate)
		Select @UserID, UserRole, Getdate(), '2050-12-31' from @tvpUserDetails
	End
End
--Update
Create procedure DNS_UPDUserDetails
(
@tvpUserDetails TVPUserDetails  readonly
)
as
Begin 

declare @UserID int
	If  exists( Select UD.UserName from UserDetails UD,  @tvpUserDetails TVPUD 
		       where UD.UserName = TVPUD.UserName)
	Begin 
		Update UserDetails 
		Set FirstName = TVPUD.FirstName, 
		LastName = TVPUD.LastName,
		Email = TVPUD.Email, IsActive = TVPUD.IsActive
		From @tvpUserDetails TVPUD 
		Where UserDetails.UserID = TVPUD.UserID	 
	End

	If  exists( Select UR.UserID from UserRole UR,  @tvpUserDetails TVPUD 
		       where UR.UserID = TVPUD.UserID)
	Begin 
		Update UserRole
		Set 
		TermDate = GETDATE()
		From @tvpUserDetails TVPUD 
		Where UserRole.UserID = TVPUD.UserID	 
	End
	
	Insert UserRole(UserID, UserRole, BeginDate, TermDate)
	Select UserID, UserRole, Getdate(), '2050-12-31' from @tvpUserDetails
End

go
-- Insert Query 
Declare @tvpUserDetails TVPUserDetails
Insert @tvpUserDetails values(0,'Mahe','Mahe','M','Mahe@DNS.com','Developer',1)
Exec DNS_INSUserDetails @tvpUserDetails
go
select * from UserDetails
select * from UserRole
go
-- Update Query 
Declare @tvpUserDetails TVPUserDetails
Insert @tvpUserDetails values(6,'Mahe','Mahe','M','MaheM@DNS.com','Admin',1)
Exec DNS_UPDUserDetails @tvpUserDetails
go
select * from UserDetails
select * from UserRole