CREATE TABLE [dbo].[User] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[UserName] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserPassword] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserEmail] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserEmailTypeID] [int] NOT NULL ,
	[UserEmailVerify] [bit] NOT NULL ,
	[UserLastLogin] [datetime] NOT NULL ,
	[UserRegistered] [datetime] NOT NULL ,
	[UserLastModified] [datetime] NOT NULL ,
	[UserLastModifiedBy] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserType] (
	[UserTypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserLoginPage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserTypeLastModified] [datetime] NOT NULL ,
	[UserTypeLastModifiedBy] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserTypes] (
	[UserTypesID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[UserTypeID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User] WITH NOCHECK ADD 
	CONSTRAINT [PK_User] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserType] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserType] PRIMARY KEY  CLUSTERED 
	(
		[UserTypeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserTypes] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserTypes] PRIMARY KEY  CLUSTERED 
	(
		[UserTypesID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User] ADD 
	CONSTRAINT [FK_User_EmailType] FOREIGN KEY 
	(
		[UserEmailTypeID]
	) REFERENCES [dbo].[EmailType] (
		[EmailTypeID]
	)
GO

ALTER TABLE [dbo].[UserTypes] ADD 
	CONSTRAINT [FK_UserTypes_User] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[User] (
		[ID]
	),
	CONSTRAINT [FK_UserTypes_UserType] FOREIGN KEY 
	(
		[UserTypeID]
	) REFERENCES [dbo].[UserType] (
		[UserTypeID]
	)
GO

CREATE procedure proc_AddUserTypes
	@UserID int, 
	@UserTypeID int, 
	@UserTypesID int output
as
	declare @ErrorCode int
	select @ErrorCode = @@Error
	
	declare @TransactionCountOnEntry int
	
if @ErrorCode = 0
begin
	select @TransactionCountOnEntry = @@TranCount
	begin transaction
		insert
			UserTypes
			(
			UserID, 
			UserTypeID
			)
		values
			(
			@UserID, 
			@UserTypeID
			)
		
		select @UserTypesID = @@Identity, @ErrorCode = @@Error
end

if @@TranCount > @TransactionCountOnEntry
begin
	if @ErrorCode = 0
		commit transaction
	else
		rollback transaction
end

return @ErrorCode
GO

CREATE procedure proc_DeleteUserTypes
	@UserTypesID int
as
	declare @ErrorCode int
	select @ErrorCode = @@Error
	
	declare @TransactionCountOnEntry int
	
if @ErrorCode = 0
begin
	select @TransactionCountOnEntry = @@TranCount
	begin transaction
		delete
			
		from
			UserTypes
		where
			UserTypes.UserTypesID = @UserTypesID
		
		select @ErrorCode = @@Error
end

if @@TranCount > @TransactionCountOnEntry
begin
	if @ErrorCode = 0
		commit transaction
	else
		rollback transaction
end

return @ErrorCode
GO

CREATE procedure proc_UpdateUser
	@ID int, 
	@Password varchar(16), 
	@Email varchar(255), 
	@EmailTypeID int, 
	@LastModifiedBy int,
	@UserTypeID varchar(600)
as
	declare @ErrorCode int
	select @ErrorCode = @@Error
	
	declare @TransactionCountOnEntry int
	
if @ErrorCode = 0
begin
	select @TransactionCountOnEntry = @@TranCount
	begin transaction
		update
			[User]
		set
			[User].UserPassword = @Password, 
			[User].UserEmail = @Email, 
			[User].UserEmailTypeID = @EmailTypeID, 
			[User].UserLastModified = getdate(),
			[User].UserLastModifiedBy = @LastModifiedBy
		where
			[User].ID = @ID
		
		select @ErrorCode = @@Error
end
	
if @ErrorCode = 0
begin
	exec @ErrorCode = proc_ProcessUserTypes @ID, @UserTypeID
end

if @@TranCount > @TransactionCountOnEntry
begin
	if @ErrorCode = 0
		commit transaction
	else
		rollback transaction
end

return @ErrorCode
GO

CREATE procedure proc_ProcessUserTypes
	@ID int, 
	@UserTypeID varchar(600)
as
	declare @ErrorCode int
	select @ErrorCode = @@Error
	
	declare @TransactionCountOnEntry int
	
	declare @CurUserTypeID int
	declare @Pos int
	
if @ErrorCode = 0
begin
	select @UserTypeID = ltrim(rtrim(@UserTypeID))+ ','
	select @Pos = charindex(',', @UserTypeID, 1)
	
	if replace(@UserTypeID, ',', '') <> ''
	begin
		while @Pos > 0
		begin
			select @CurUserTypeID = ltrim(rtrim(left(@UserTypeID, @Pos - 1)))
			if @CurUserTypeID <> ''
			begin
				--NEED HELP HERE
				--Add
				--exec @ErrorCode = proc_AddUserTypes @CurUserTypeID
				--Delete
				--exec @ErrorCode = proc_DeleteUserTypes @CurUserTypeID
			end
			select @UserTypeID = right(@UserTypeID, len(@UserTypeID) - @Pos)
			select @Pos = charindex(',', @UserTypeID, 1)
		end
	end
end

if @@TranCount > @TransactionCountOnEntry
begin
	if @ErrorCode = 0
		commit transaction
	else
		rollback transaction
end

return @ErrorCode

GO