Cacher is the code snippet organizer for pro developers

-- =============================================
-- Author:		Chandrashekar Naik D
-- Create date: 2015-06-25
-- Description:	Insert Delegation 
-- =============================================
--SELECT * FROM UserDelegateMapping UDM
CREATE PROCEDURE USP_SaveUserDelegation @centerId INT, @delegateFromUserId INT, @strUserIds NVARCHAR(1000), @LogInUserId INT
AS
BEGIN
	--DECLARE @xmldelegateToUsers VARCHAR(max) = '<Root><Row><UserId>1</UserId></Row><Row><UserId>2</UserId></Row></Root>'
	--IF @strUserIds <> ''
	--BEGIN
	--DECLARE @strUserIds VARCHAR(1000) = '1494,1245'
	DECLARE @tblSourceUsers TABLE (Id INT IDENTITY(1, 1), UserId INT)
	DECLARE @tblRecordsToDelete TABLE (Id INT IDENTITY(1, 1), UserId INT)
	DECLARE @tblRecordsToInsert TABLE (Id INT IDENTITY(1, 1), UserId INT)
	DECLARE @UserId INT
	DECLARE @I INT = 1, @I_RowCount INT

	IF @strUserIds <> ''
	BEGIN
		INSERT INTO @tblSourceUsers
		SELECT CONVERT(INT, Value)
		FROM [dbo].[Spliter](@strUserIds, ',')
	END

	--Delete recods which are not in incoming list and exists in user table
	INSERT INTO @tblRecordsToDelete
	SELECT Ltbl.DelegateToUserId
	FROM UserDelegateMapping Ltbl
	LEFT JOIN @tblSourceUsers Rtbl ON Ltbl.DelegateToUserId = Rtbl.UserId
	WHERE Ltbl.DelegateFromUserId = @delegateFromUserId
		AND Rtbl.UserId IS NULL

	IF EXISTS (
			SELECT 1
			FROM @tblRecordsToDelete
			)
	BEGIN
		--Delete
		DELETE UDM
		FROM UserDelegateMapping UDM
		INNER JOIN @tblRecordsToDelete tbl ON UDM.DelegateToUserId = tbl.UserId
		WHERE UDM.DelegateFromUserId = @delegateFromUserId

		SELECT @I_RowCount = COUNT(*)
		FROM @tblRecordsToDelete

		SET @I = 1

		--Loop for each user and remove proxy manager role
		WHILE (@I <= @I_RowCount)
		BEGIN
			SELECT @UserId = UserId
			FROM @tblRecordsToDelete
			WHERE Id = @I

			--Role:15 is Proxy Manager Role
			EXEC Usp_DeactivateAssignedUsers @UserId = @UserId, @CenterId = @centerId, @RoleId = 15, @ModifiedBy = @LogInUserId

			SET @I = @I + 1
		END
	END

	--Insert records which are in incoming user list and not in destination user table
	INSERT INTO @tblRecordsToInsert
	SELECT Ltbl.UserId
	FROM @tblSourceUsers Ltbl
	LEFT JOIN UserDelegateMapping Rtbl ON Ltbl.UserId = Rtbl.DelegateToUserId
		AND Rtbl.DelegateFromUserId = @delegateFromUserId
	WHERE Rtbl.DelegateToUserId IS NULL

	IF EXISTS (
			SELECT 1
			FROM @tblRecordsToInsert
			)
	BEGIN
		--Insert
		INSERT INTO UserDelegateMapping (DelegateFromUserId, DelegateToUserId, IsActive, CreatedBy, CreatedDate)
		SELECT @delegateFromUserId, tbl.UserId, 1, @LogInUserId, GETDATE()
		FROM @tblRecordsToInsert tbl

		SELECT @I_RowCount = COUNT(*)
		FROM @tblRecordsToInsert

		SET @I = 1

		--Loop for each user and add proxy manager role
		WHILE (@I <= @I_RowCount)
		BEGIN
			SELECT @UserId = UserId
			FROM @tblRecordsToInsert
			WHERE Id = @I

			--Role:15 is Proxy Manager Role			
			EXEC Usp_AddCenterAdminPerUserId @userID = @UserId, @roleid = 15, @CenterId = @centerId, @CreatedBy = @LogInUserId

			SET @I = @I + 1
		END
	END
END