SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

DECLARE @UserIDNumber int
DECLARE @DepartmentList varchar(32)
DECLARE @DepartmentNumber char(1)
DECLARE @PK_Department int
DECLARE @UserIDCode varchar(255)

DECLARE PersonnelCur CURSOR FAST_FORWARD LOCAL FOR
	SELECT UserIDNumber, UserIDCode, DepartmentNumbers FROM Personel WITH (NOLOCK)
OPEN PersonnelCur

FETCH NEXT FROM PersonnelCur INTO @UserIDNumber, @UserIDCode, @DepartmentList
WHILE @@fetch_status = 0
BEGIN
	PRINT 'Processing User: ' + @UserIDCode + '(' + CONVERT(varchar, @UserIDNumber, 0) + ') - DepartmentList: ' + @DepartmentList

	IF LEN(@DepartmentList) > 0
	BEGIN
		DECLARE DepartmentCur CURSOR FAST_FORWARD LOCAL FOR
			SELECT PK_Department, DepartmentNumber FROM Departments WITH (NOLOCK)
		OPEN DepartmentCur

		FETCH NEXT FROM DepartmentCur INTO @PK_Department, @DepartmentNumber
		WHILE @@fetch_status = 0
		BEGIN
			IF (DBO.DepartmentFound(@DepartmentList, @DepartmentNumber) <> '') AND (NOT EXISTS(SELECT * FROM PersonelDepartmentsXTab WHERE FK_Department = @PK_Department AND UserIDNumber = @UserIDNumber))
			BEGIN
				PRINT ' -- Adding missing Department (' + Convert(varchar, @PK_Department, 0) + ')'
				INSERT INTO PersonelDepartmentsXTab (UserIDNumber, FK_Department) VALUES (@UserIDNumber, @PK_Department)
			END

			FETCH NEXT FROM DepartmentCur INTO @PK_Department, @DepartmentNumber
		END
		CLOSE DepartmentCur
		DEALLOCATE DepartmentCur
	END

	FETCH NEXT FROM PersonnelCur INTO @UserIDNumber, @UserIDCode, @DepartmentList
END
CLOSE PersonnelCur
DEALLOCATE PersonnelCur

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
