dbo.QBM_PColumnAdd

1   create   procedure QBM_PColumnAdd (@TableName varchar(30) , @ColumnName varchar(30) , @TypeDef nvarchar(64) , @UID_DialogColumn varchar(38) =
2 null , @UID_DialogTable varchar(38) = null ) as begin declare @DebugSwitch int = 0 declare @Cmd nvarchar(max) declare @UID_DialogTableIntern varchar(38
3) declare @datatype nvarchar(64) declare @OwnerDB varchar(3) declare @OwnerColumn varchar(3) declare @ElementsToKeep int declare @ViewsWithElementsToKeep
4 QBM_YCursorBuffer declare @ViewName varchar(30) declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare @XUser nvarchar(64
5) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY if not exists (select top 1 1 from QBM_VSchemaColumns c where
6 c.TABLE_NAME = @TableName and c.COLUMN_NAME = @ColumnName ) begin select @Cmd = '
7	alter table ' + @TableName + ' add ' + @ColumnName + ' ' + @TypeDef
8 if @DebugSwitch > 0 begin print @cmd end exec sp_executesql @cmd end  select top 1 @datatype = c.DATA_TYPE from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME
9 = @TableName and c.COLUMN_NAME = @ColumnName if @datatype in ('bit', 'int', 'bigint', 'float', 'real') begin select @Cmd = concat('
10	update ' , @TableName
11 , ' set ' , @ColumnName , ' = 0  '  ,' where ' , @ColumnName , ' is null' ) if @DebugSwitch > 0 begin print @cmd end exec sp_executesql @cmd  if exists
12 (select top 1 1 from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME = @TableName and c.COLUMN_NAME = @ColumnName and c.COLUMN_DEFAULT is null ) begin
13 select @cmd = concat( 'sp_bindefault ''DezimalNULL'', ''', @TableName, '.', @ColumnName, '''') exec sp_executesql @cmd end  end  if @UID_DialogColumn 
14is null begin goto endLabel  end if not exists (select top 1 1 from sys.tables t where t.name = 'DialogColumn' ) begin goto endLabel end if @UID_DialogTable
15 is null  begin select top 1 @UID_DialogTableIntern = t.UID_DialogTable from DialogTable t where t.TableName = @TableName end else begin select @UID_DialogTableIntern
16 = @UID_DialogTable end if @UID_DialogTableIntern is null begin    goto EndLabel   end   select @OwnerDB = dbo.QBM_FGIDBOwner() select @OwnerColumn = dbo.QBM_FCVGUIDToModuleOwner
17(@UID_DialogColumn) select @cmd = 'select name from sys.views where name like ''___[_]VElementsToKeep''' insert into @ViewsWithElementsToKeep (ObjectKey1
18) exec sp_executesql @cmd select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY select
19 @ElementsToKeep = 0 while @ElementIndex <= @ElementLast and @ElementsToKeep = 0 begin select top 1 @ViewName = v.ObjectKey1 from @ViewsWithElementsToKeep
20 v where v.ElementIndex = @ElementIndex select @ElementsToKeep = 0 select @cmd = 'select top 1 1 from ' + @ViewName + ' where SourceModule = ''' + @OwnerColumn
21 + '''' exec sp_executesql @cmd select @ElementsToKeep = @@ROWCOUNT select @ElementIndex += 1 end if @OwnerColumn <> 'CCC' and @OwnerColumn <> @OwnerDB
22 and @ElementsToKeep = 0 begin  goto EndLabel end if @UID_DialogTable > ' '  and not exists (select top 1 1 from DialogTable t where t.TableName = @TableName
23 ) begin insert into DialogTable( TableName, UID_DialogTable, TableType, XObjectKey , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated ) values
24 ( @TableName, @UID_DialogTableIntern, 'T', dbo.QBM_FCVElementToObjectKey1('DialogTable', '', @UID_DialogTableIntern) , @Xdate, @Xdate, @XUser, @XUser 
25) end  if not exists (select top 1 1 from DialogColumn c where c.UID_DialogColumn = @UID_DialogColumn ) begin insert into DialogColumn (UID_DialogColumn
26, UID_DialogTable, XObjectKey , ColumnName) select @UID_DialogColumn, @UID_DialogTableIntern, dbo.QBM_FCVElementToObjectKey1('DialogColumn', '', @UID_DialogColumn
27) , @ColumnName end update DialogColumn set SchemaDataType = x.DATA_TYPE , SchemaDataLen = x.CHARACTER_MAXIMUM_LENGTH , Datatype = dbo.QBM_FCVSchemaDatatypeToIntern
28(x.DATA_TYPE, x.CHARACTER_MAXIMUM_LENGTH) , XUserInserted = 'QBM_PColumnAdd' , XUserUpdated = 'QBM_PColumnAdd' , XDateInserted = GETUTCDATE() , XDateUpdated
29 = GETUTCDATE() from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable join INFORMATION_SCHEMA.COLUMNS x on t.TableName = x.TABLE_NAME
30 and c.ColumnName = x.COLUMN_NAME where ( isnull(SchemaDataType, '') <> x.DATA_TYPE or isnull(schemaDataLen, 0) <> isnull(x.CHARACTER_MAXIMUM_LENGTH, 0
31) or isnull(Datatype, -1) = dbo.QBM_FCVSchemaDatatypeToIntern(x.DATA_TYPE, x.CHARACTER_MAXIMUM_LENGTH) ) and c.UID_DialogColumn = @UID_DialogColumn END
32 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
33 END CATCH endLabel: return end 
34