text: ﻿CREATE PROCEDURE [core].[base_Field_Add]
  	@TableName varchar(64),
  	@Name varchar(64),
  	@Title varchar(64),
  	@Type int,
  	@IsHidden bit,
  	@IsDeleted bit,
  	@IsSystem bit,
  	@IsAutoGenerated bit,
  	--@InternalName varchar(64),
  	@Validations [Validations] READONLY,
  	@Id int = 0,
  	@FieldIdentityInsert bit = 0,
  	@ValidationIdentityInsert bit = 0,
  	@ShowInUi bit = 1,
  	@UiIndex int = 0,
  	@Choices [Choices] READONLY,
  	@ChoiceIdentityInsert bit = 0
  AS
  BEGIN
  	declare @AlterTableSql nvarchar(max)
  	declare @ItemId int
  	declare @DataType varchar(64)
  
  	if @Type=0
  		set @DataType = 'nvarchar(255)'
  	else if @Type=1
  		set @DataType = 'nvarchar(max)'
  	else if @Type=2
  		set @DataType = 'float'
  	else if @Type=3
  		set @DataType = 'datetime'
  	else if @Type=4
  		set @DataType = 'bit'
  	else if @Type=5
  		set @DataType = 'nvarchar(255)'
  
  	set @AlterTableSql = N'ALTER TABLE ' + @TableName + ' ADD ' + @Name + ' ' + @DataType + ' NULL'
  	EXECUTE sp_executesql @AlterTableSql
  	
  	declare @fieldInsert nvarchar(max)
  
  	IF @FieldIdentityInsert = 0
  	BEGIN
  		INSERT INTO [Fields] ([TableName],[Name],[Title],[Type],[IsHidden],[IsDeleted],[IsSystem],[IsAutoGenerated],[ShowInUi],[UiIndex]) -- ,[InternalName]
  		VALUES (@TableName, @Name, @Title, @Type, @IsHidden, @IsDeleted, @IsSystem, @IsAutoGenerated,@ShowInUi,@UiIndex)-- , @InternalName
  		
  		select @ItemId = SCOPE_IDENTITY()
  	END
  	ELSE
  	BEGIN
  
  		SET @fieldInsert = 
  'SET IDENTITY_INSERT [core].[Fields] ON
  INSERT INTO [core].[Fields] ([Id], [TableName],[Name],[Title],[Type],[IsHidden],[IsDeleted],[IsSystem],[IsAutoGenerated],[ShowInUi],[UiIndex])
  VALUES (' + CAST(@Id AS VARCHAR) + ', ''' + @TableName + ''', ''' + @Name + ''', ''' + @Title + ''', ''' + @Type + ''', 
  ' + CAST(@IsHidden AS VARCHAR) + ', ' + CAST(@IsDeleted AS VARCHAR) + ', ' + CAST(@IsSystem AS VARCHAR) + ', ' + CAST(@IsAutoGenerated AS VARCHAR) + ', ' + CAST(@ShowInUi AS VARCHAR) + ', ' + CAST(@UiIndex AS VARCHAR) + ') 
  SET IDENTITY_INSERT [core].[Fields] OFF' 
  
  		execute(@fieldInsert)
  
  		select @ItemId = @Id
  	END 
  	
  	IF @ValidationIdentityInsert = 0
  	BEGIN
  		insert into [FieldValidations]([FieldId], [ValidatorId], [Message], [Data],[FieldName]) 
  		select @ItemId, [ValidatorId], [Message], [Data],@Name
  		from @Validations
  	END
  	ELSE
  	BEGIN
  		
  		declare @VId int
  		declare @VData varchar(max)
  		declare @VMessage varchar(max)
  		declare @VValidatorId uniqueidentifier
  		
  		DECLARE @ValidationCursor CURSOR
  		SET @ValidationCursor = CURSOR FAST_FORWARD
  		FOR
  		SELECT [Id],[ValidatorId], [Message], [Data]
  		FROM   @Validations 
  		OPEN @ValidationCursor
  		
  		FETCH NEXT FROM @ValidationCursor
  		INTO @VId,@VValidatorId,@VMessage,@VData
  
  		WHILE @@FETCH_STATUS = 0
  		BEGIN
  			SET @fieldInsert = 
  'SET IDENTITY_INSERT [core].[FieldValidations] ON
  insert into [core].[FieldValidations]([Id], [FieldId], [ValidatorId], [Message], [Data],[FieldName]) 
  values (' + CAST(@VId AS VARCHAR) + ', ' + CAST(@ItemId AS VARCHAR) + ', ''' + CAST(@VValidatorId AS VARCHAR(36)) + ''',
  ''' + @VMessage + ''', ''' + @VData + ''', ''' + @Name + ''')
  SET IDENTITY_INSERT [core].[FieldValidations] OFF'
  			execute(@fieldInsert)
  
  			FETCH NEXT FROM @ValidationCursor
  			INTO @VId,@VValidatorId,@VMessage,@VData
  		END
  
  		CLOSE @ValidationCursor
  		DEALLOCATE @ValidationCursor
  	END 
  	
  	if @Type=5
  	begin
  		if @ChoiceIdentityInsert = 0
  		begin
  			insert into [FieldChoices] ([FieldId], [Text])
  			select @ItemId, [Text]
  			from @Choices
  		end
  		else
  		begin
  			declare @CId int
  			declare @CText varchar(max)
  		
  			DECLARE @ChoicesCursor CURSOR
  			SET @ChoicesCursor = CURSOR FAST_FORWARD
  			FOR
  			SELECT [Id], [Text]
  			FROM   @Choices 
  			OPEN @ChoicesCursor
  		
  			FETCH NEXT FROM @ChoicesCursor
  			INTO @CId,@CText
  
  			WHILE @@FETCH_STATUS = 0
  			BEGIN
  				SET @fieldInsert = 
  	'SET IDENTITY_INSERT [core].[FieldChoices] ON
  	insert into [core].[FieldChoices]([Id], [FieldId], [Text]) 
  	values (' + CAST(@CId AS VARCHAR) + ', ' + CAST(@ItemId AS VARCHAR) + ', ''' + @CText + ''')
  	SET IDENTITY_INSERT [core].[FieldChoices] OFF'
  				execute(@fieldInsert)
  
  				FETCH NEXT FROM @ChoicesCursor
  				INTO @CId,@CText
  			END
  
  			CLOSE @ChoicesCursor
  			DEALLOCATE @ChoicesCursor
  		end
  	end
  
  	SELECT @ItemId
  END
  