public void VerifyColumnType(CorrelationProperty correlationProperty) { var columnType = GetColumnType(correlationProperty.Type); var name = correlationProperty.Name; writer.Write($@" declare @dataType_{name} nvarchar(max); set @dataType_{name} = ( select data_type from information_schema.columns where table_name = ' + @tableName + ' and column_name = 'Correlation_{name}' ); if (@dataType_{name} <> '{columnType}') throw 50000, 'Incorrect data type for Correlation_{name}', 0 "); }
public void WriteCreateIndex(CorrelationProperty correlationProperty) { var name = correlationProperty.Name; writer.Write($@" select count(*) into @exist from information_schema.statistics where table_schema = database() and index_name = 'Index_Correlation_{name}' and table_name = @tableName; set @query = IF( @exist <= 0, concat('create unique index Index_Correlation_{name} on ', @tableName, '(Correlation_{name})'), 'select \'Index Exists\' status'); prepare statment from @query; execute statment; deallocate prepare statment; "); }
public void AddProperty(CorrelationProperty correlationProperty) { var columnType = GetColumnType(correlationProperty.Type); var name = correlationProperty.Name; writer.Write($@" select count(*) into @exist from information_schema.columns where table_schema = database() and column_name = 'Correlation_{name}' and table_name = @tableName; set @query = IF( @exist <= 0, concat('alter table ', @tableName, ' add column Correlation_{name} {columnType}'), 'select \'Column Exists\' status'); prepare statment from @query; execute statment; deallocate prepare statment; "); }
public void AddProperty(CorrelationProperty correlationProperty) { var columnType = GetColumnType(correlationProperty.Type); var name = correlationProperty.Name; writer.Write($@" if not exists ( select * from sys.columns where name = 'Correlation_{name}' and object_id = object_id(@tableName) ) begin declare @createColumn_{name} nvarchar(max); set @createColumn_{name} = ' alter table ' + @tableName + ' add Correlation_{name} {columnType};'; exec(@createColumn_{name}); end "); }
public void WriteCreateIndex(CorrelationProperty correlationProperty) { var name = correlationProperty.Name; writer.Write($@" if not exists ( select * from sys.indexes where name = 'Index_Correlation_{name}' and object_id = object_id(@tableName) ) begin declare @createIndex_{name} nvarchar(max); set @createIndex_{name} = ' create unique index Index_Correlation_{name} on ' + @tableName + '(Correlation_{name}) where Correlation_{name} is not null;'; exec(@createIndex_{name}); end "); }
public void VerifyColumnType(CorrelationProperty correlationProperty) { var columnType = GetColumnType(correlationProperty.Type); var name = correlationProperty.Name; writer.Write($@" set @column_type_{name} = ( select column_type from information_schema.columns where table_schema = database() and table_name = @tableName and column_name = 'Correlation_{name}' ); set @query = IF( @column_type_{name} <> '{columnType}', 'SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = \'Incorrect data type for Correlation_{name}\'', 'select \'Column Type OK\' status'); prepare statment from @query; execute statment; deallocate prepare statment; "); }