dbo.QBM_PColumnChangeDataType

1      create   procedure QBM_PColumnChangeDataType (@TableName varchar(30) , @ColumnName varchar(30) , @TypeDefNew nvarchar(64) , @ConvertFunction
2 nvarchar(1000) = null  ) as begin declare @Cmd nvarchar(max) declare @DebugSwitch int = 0 declare @UID_DialogColumn varchar(38) declare @XUser nvarchar
3(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY exec QBM_PTriggerDrop '%', @TableName exec QBM_PIndexDrop
4 @TableName, '%' exec QBM_PConstraintFKDrop @TableName, '%', '%' exec QBM_PConstraintFKDrop '%', '%', @TableName select @cmd = '
5exec sp_rename ''' + 
6@TableName + '.' + @ColumnName + ''', ''kannweg'', ''column''
7	' if @DebugSwitch > 0 begin print @cmd end exec sp_executesql @cmd select @Cmd = '
8alter table '
9 + @TableName + ' add ' + @ColumnName + ' ' + @TypeDefNew if @DebugSwitch > 0 begin print @cmd end exec sp_executesql @cmd select @Cmd = concat('
10	update '
11 , @TableName , ' set ' , @ColumnName , ' = ' , case when @ConvertFunction > ' ' then @ConvertFunction else 'kannweg' end , case when dbo.QBM_FGIColumnExistsInSchema
12(@TableName, 'XDateUpdated') = 1 and @ColumnName not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name
13(@@procid) + '''' else '' end ) if @DebugSwitch > 0 begin print @cmd end exec sp_executesql @cmd if @TypeDefNew in ('bit', 'int', 'real', 'float', 'bigint'
14) begin select @Cmd = concat( 'sp_bindefault ''DezimalNULL'', ''' , @TableName , '.' , @ColumnName , '''') exec sp_executesql @cmd end   exec QBM_PColumnDrop
15 @tablename, 'kannweg'  if not exists (select top 1 1 from sys.tables t where t.name = 'DialogColumn' ) begin goto endLabel end select top 1 @UID_DialogColumn
16 = c.UID_DialogColumn from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.ColumnName = @ColumnName and t.TableName 
17= @TableName update DialogColumn set SchemaDataType = x.DATA_TYPE , SchemaDataLen = x.CHARACTER_MAXIMUM_LENGTH , Datatype = dbo.QBM_FCVSchemaDatatypeToIntern
18(x.DATA_TYPE, x.CHARACTER_MAXIMUM_LENGTH)  , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t on c.UID_DialogTable 
19= t.UID_DialogTable join INFORMATION_SCHEMA.COLUMNS x on t.TableName = x.TABLE_NAME and c.ColumnName = x.COLUMN_NAME where ( isnull(SchemaDataType, '')
20 <> x.DATA_TYPE or isnull(schemaDataLen, 0) <> isnull(x.CHARACTER_MAXIMUM_LENGTH, 0) or isnull(Datatype, -1) = dbo.QBM_FCVSchemaDatatypeToIntern(x.DATA_TYPE
21, x.CHARACTER_MAXIMUM_LENGTH) ) and c.UID_DialogColumn = @UID_DialogColumn END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar
22(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 
23