text: ﻿CREATE PROCEDURE [bll].[Units_Validate__Save]
  	@Entities [dbo].[UnitList] READONLY,
  	@Top INT = 200,
  	@IsError BIT OUTPUT
  AS
  BEGIN
  	SET NOCOUNT ON;
  
  	DECLARE @ValidationErrors [dbo].[ValidationErrorList];
  	
      -- Non zero Ids must exist
      INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST([Index] AS NVARCHAR (255)) + '].Id',
  		N'Error_TheId0WasNotFound',
  		CAST([Id] As NVARCHAR (255))
      FROM @Entities
      WHERE [Id] <> 0
  	AND [Id] NOT IN (SELECT [Id] from [dbo].[Units]);
  
  	-- Code must not be already in the back end
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST(FE.[Index] AS NVARCHAR (255)) + '].Code',
  		N'Error_TheCode0IsUsed',
  		FE.[Code]
  	FROM @Entities FE 
  	JOIN [dbo].[Units] BE ON FE.[Code] = BE.[Code]
  	WHERE
  		FE.[Code] IS NOT NULL
  	AND BE.[Code] IS NOT NULL
  	AND FE.[Id] <> BE.[Id];
  
  	-- Code must not be duplicated in the uploaded list
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST([Index] AS NVARCHAR (255)) + '].Code',
  		N'Error_TheCode0IsDuplicated',
  		[Code]
  	FROM @Entities
  	WHERE [Code] IN (
  		SELECT [Code]
  		FROM @Entities
  		WHERE [Code] IS NOT NULL
  		GROUP BY [Code]
  		HAVING COUNT(*) > 1
  	)
  
  	-- Name must not exist in the db
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST(FE.[Index] AS NVARCHAR (255)) + '].Name',
  		N'Error_TheName0IsUsed',
  		FE.[Name]
  	FROM @Entities FE 
  	JOIN [dbo].[Units] BE ON FE.[Name] = BE.[Name]
  	WHERE FE.[Id] <> BE.[Id];
  
  	-- Name2 must not exist in the db
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST(FE.[Index] AS NVARCHAR (255)) + '].Name2',
  		N'Error_TheName0IsUsed',
  		FE.[Name2]
  	FROM @Entities FE 
  	JOIN [dbo].[Units] BE ON FE.[Name2] = BE.[Name2]
  	WHERE FE.[Id] <> BE.[Id];
  
  	-- Name3 must not exist in the db
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST(FE.[Index] AS NVARCHAR (255)) + '].Name3',
  		N'Error_TheName0IsUsed',
  		FE.[Name3]
  	FROM @Entities FE 
  	JOIN [dbo].[Units] BE ON FE.[Name3] = BE.[Name3]
  	WHERE FE.[Id] <> BE.[Id];
  
  	-- Name must be unique in the uploaded list
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST([Index] AS NVARCHAR (255)) + '].Name',
  		N'Error_TheName0IsDuplicated',
  		[Name]
  	FROM @Entities
  	WHERE [Name] IN (
  		SELECT [Name] FROM @Entities
  		GROUP BY [Name]
  		HAVING COUNT(*) > 1
  	);
  
  	-- Name2 must be unique in the uploaded list
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST([Index] AS NVARCHAR (255)) + '].Name2',
  		N'Error_TheName0IsDuplicated',
  		[Name2]
  	FROM @Entities
  	WHERE [Name2] IN (
  		SELECT [Name2] FROM @Entities
  		WHERE [Name2] IS NOT NULL
  		GROUP BY [Name2]
  		HAVING COUNT(*) > 1
  	);
  
  	-- Name3 must be unique in the uploaded list
  	INSERT INTO @ValidationErrors([Key], [ErrorName], [Argument0])
  	SELECT DISTINCT TOP (@Top)
  		'[' + CAST([Index] AS NVARCHAR (255)) + '].Name3',
  		N'Error_TheName0IsDuplicated',
  		[Name3]
  	FROM @Entities
  	WHERE [Name3] IN (
  		SELECT [Name3] FROM @Entities
  		WHERE [Name3] IS NOT NULL
  		GROUP BY [Name3]
  		HAVING COUNT(*) > 1
  	);
  
  	-- Set @IsError
  	SET @IsError = CASE WHEN EXISTS(SELECT 1 FROM @ValidationErrors) THEN 1 ELSE 0 END;
  
  	-- Return Errors
  	SELECT TOP(@Top) * FROM @ValidationErrors;
  END;