DP44/DataPRO_sql/dbo.sp_DBImportUsers.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DBImportUsers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportUsers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportUsers] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_DBImportUsers]
		 @Users						xml
		,@errorNumber				int				output
		,@errorMessage				nvarchar(250)	output 
AS
BEGIN
	set @errorNumber = 0
	set @errorMessage = space(0)

	declare @tUsers table (
				 UserName		nvarchar(50) 
				,DisplayName	nvarchar(50) 
				,[Password]		nvarchar(2048) 
				,[Role]			varchar(50)
				,LocalOnly		bit
				,UISettings		varchar(max)
				,UIVisibility	varchar(max)
				,Processed		bit
	
	)
	insert into @tUsers	(UserName		
						,DisplayName	
						,[Password]		
						,[Role]			
						,LocalOnly		
						,UISettings		
						,UIVisibility	
						,Processed)
				select
					 t.x.value('(./UserName)[1]', 'nvarchar(50)')
					,t.x.value('(./DisplayName)[1]', 'nvarchar(50)')
					,t.x.value('(./Password)[1]', 'nvarchar(2048)')
					,t.x.value('(./Role)[1]', 'varchar(50)')
					,t.x.value('(./LocalOnly)[1]', 'nvarchar(2048)')
					,t.x.value('(./IUIItemPermissions)[1]', 'varchar(max)')
					,t.x.value('(./IUIItemVisibility)[1]', 'varchar(max)')
					,0
			from @Users.nodes('/User') t(x) option (OPTIMIZE FOR ( @Users = NULL ))

	while (Select Count(*) From @tUsers Where Processed = 0) > 0
	Begin
		begin try
			begin transaction tUsers

				declare @UserName		nvarchar(50) 
				declare @DisplayName	nvarchar(50) 
				declare @Password		nvarchar(2048) 
				declare @Role			varchar(50)
				declare @LocalOnly		bit
				declare @UISettings		varchar(max)
				declare @UIVisibility	varchar(max)
				declare @Processed		bit
				declare @LastModified   datetime
				set @LastModified = getdate()
			declare @UserId		int
			declare @User		xml

			select top 1 	 @UserName		= UserName		
							,@DisplayName	= DisplayName	
							,@Password		= [Password]
							,@Role			= [Role]	
							,@LocalOnly		= LocalOnly		
							,@UISettings	= UISettings		
							,@UIVisibility	= UIVisibility	
							,@Processed		= Processed		
			from @tUsers Where Processed = 0

			exec [dbo].[sp_UserUpdateInsert]	 @UserName		
												,@DisplayName	
												,@Password		
												,@Role			
												,@LastModified
												,'SYSTEM'
												,@LocalOnly		
												,@UserId		output 		
												,@errorNumber	output 		
												,@errorMessage	output 		

		
			if(@UISettings != space(0))
			begin
				insert into [dbo].[UIItemSettings]
				(UserID, UIItemID, Permission, Visible)
				select @UserId, u.ID, s.UIPPropertyValue as Permission,  v.UVPropertyValue as Visible
				from  [dbo].[UIItems] u
				inner join [dbo].[foo_UserUIVisibilityRecord](@UISettings, ',') v on u.NAME = v.UVProperty
				inner join [dbo].[foo_UserUISettingsRecord](@UIVisibility, ',') s on u.NAME = s.UIPProperty
			end

			update @tUsers set [Processed] = 1 where UserName = @UserName

			commit transaction tUsers
		end try
		begin catch
			set @errorNumber = error_number()
			set @errorMessage = error_message()
			rollback transaction tUsers
		end catch;
	End
END




























GO