Back to OIM Explorer

dbo.QBM_PColumnChangeDataType

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.782 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVSchemaDatatypeToIntern source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PColumnDrop source text reference
  • references source dbo.QBM_PConstraintFKDrop source text reference
  • references source dbo.QBM_PIndexDrop source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PTriggerDrop source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL120 lines
1CREATE PROCEDURE QBM_PColumnChangeDataType(2  @TableName varchar(30),3  @ColumnName varchar(30),4  @TypeDefNew nvarchar(64),5  @ConvertFunction nvarchar(1000) = NULL6)7AS8BEGIN9  DECLARE @Cmd nvarchar(max)10  DECLARE @DebugSwitch int = 011  DECLARE @UID_DialogColumn varchar(38)12  DECLARE @XUser nvarchar(64) = object_name(@@procid)13  DECLARE @Xdate datetime = getutcdate()14  SET XACT_ABORT OFF15  BEGIN TRY16    EXEC QBM_PTriggerDrop '%',17      @TableName18    EXEC QBM_PIndexDrop @TableName,19    '%'20    EXEC QBM_PConstraintFKDrop @TableName,21    '%',22    '%'23    EXEC QBM_PConstraintFKDrop '%',24    '%',25      @TableName26    SELECT27      @cmd = '28exec sp_rename ''' + @TableName + '.' + @ColumnName + ''', ''kannweg'', ''column''29	'30    IF @DebugSwitch > 031    BEGIN32      print @cmd33    END34    EXEC sp_executesql @cmd35    SELECT @Cmd = '36alter table ' + @TableName + ' add ' + @ColumnName + ' ' + @TypeDefNew37    IF @DebugSwitch > 038    BEGIN39      print @cmd40    END41    EXEC sp_executesql @cmd42    SELECT43      @Cmd = CONCAT('44	update ',45      @TableName,46      ' set ',47      @ColumnName,48      ' = ',49    CASE50      WHEN @ConvertFunction > ' ' THEN51    @ConvertFunction52    ELSE 'kannweg'53    END,54    CASE55      WHEN dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') = 1 AND @ColumnName NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN56    ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''57    ELSE ''58    END)59    IF @DebugSwitch > 060    BEGIN61      print @cmd62    END63    EXEC sp_executesql @cmd64    IF @TypeDefNew IN('bit',65    'int',66    'real',67    'float',68    'bigint')69    BEGIN70      SELECT71        @Cmd = CONCAT('sp_bindefault ''DezimalNULL'', ''',72        @TableName,73        '.',74        @ColumnName,75        '''')76      EXEC sp_executesql @cmd77    END78    EXEC QBM_PColumnDrop @tablename,79    'kannweg'80    IF NOT EXISTS(81      SELECT TOP 1 182      FROM sys.tables t83      WHERE84        t.name = 'DialogColumn')85    BEGIN86      GOTO endLabel87    END88    SELECT TOP 1 @UID_DialogColumn = c.UID_DialogColumn89    FROM DialogColumn c90    JOIN DialogTable t91      ON c.UID_DialogTable = t.UID_DialogTable92    WHERE93      c.ColumnName = @ColumnName AND t.TableName = @TableName94    UPDATE DialogColumn95    SET SchemaDataType = x.DATA_TYPE,96    SchemaDataLen = x.CHARACTER_MAXIMUM_LENGTH,97    Datatype = dbo.QBM_FCVSchemaDatatypeToIntern(x.DATA_TYPE,98    x.CHARACTER_MAXIMUM_LENGTH),99    XDateUpdated = @Xdate,100    XUserUpdated = @XUser101    FROM DialogColumn c102    JOIN DialogTable t103      ON c.UID_DialogTable = t.UID_DialogTable104    JOIN INFORMATION_SCHEMA.COLUMNS x105      ON t.TableName = x.TABLE_NAME AND c.ColumnName = x.COLUMN_NAME106    WHERE107      (isnull(SchemaDataType, '') <> x.DATA_TYPE OR isnull(schemaDataLen, 0) <> isnull(x.CHARACTER_MAXIMUM_LENGTH,108    0) OR isnull(Datatype, -1) = dbo.QBM_FCVSchemaDatatypeToIntern(x.DATA_TYPE, x.CHARACTER_MAXIMUM_LENGTH)) AND c.UID_DialogColumn = @UID_DialogColumn109  END TRY110  BEGIN CATCH111    EXEC QBM_PSessionErrorAdd DEFAULT112    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()113    RAISERROR(@Rethrow,114    18,115    1)116      WITH NOWAIT117  END CATCH118  endLabel:119  RETURN120END
Open raw exported source
SQL ยท Raw23 lines
1      create   procedure QBM_PColumnChangeDataType (@TableName varchar(30) , @ColumnName varchar(30) , @TypeDefNew nvarchar(64) , @ConvertFunction2 nvarchar(1000) = null  ) as begin declare @Cmd nvarchar(max) declare @DebugSwitch int = 0 declare @UID_DialogColumn varchar(38) declare @XUser nvarchar3(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY exec QBM_PTriggerDrop '%', @TableName exec QBM_PIndexDrop4 @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_FGIColumnExistsInSchema12(@TableName, 'XDateUpdated') = 1 and @ColumnName not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name13(@@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_PColumnDrop15 @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_DialogColumn16 = 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_FCVSchemaDatatypeToIntern18(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_TYPE21, x.CHARACTER_MAXIMUM_LENGTH) ) and c.UID_DialogColumn = @UID_DialogColumn END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar22(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 23