Back to OIM Explorer

dbo.QBM_PColumnAdd

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.751 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_FCVElementToObjectKey1 source text reference
references source dbo.QBM_FCVGUIDToModuleOwner source text reference
references source dbo.QBM_FCVSchemaDatatypeToIntern source text reference
references source dbo.QBM_FGIDBOwner source text reference
references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

dbo.QBM_FSQColumAdd

Complete Source

1CREATE PROCEDURE QBM_PColumnAdd(2  @TableName varchar(30),3  @ColumnName varchar(30),4  @TypeDef nvarchar(64),5  @UID_DialogColumn varchar(38) = NULL,6  @UID_DialogTable varchar(38) = NULL7)8AS9BEGIN10  DECLARE @DebugSwitch int = 011  DECLARE @Cmd nvarchar(max)12  DECLARE @UID_DialogTableIntern varchar(38)13  DECLARE @datatype nvarchar(64)14  DECLARE @OwnerDB varchar(3)15  DECLARE @OwnerColumn varchar(3)16  DECLARE @ElementsToKeep int17  DECLARE @ViewsWithElementsToKeep QBM_YCursorBuffer18  DECLARE @ViewName varchar(30)19  DECLARE @ElementLast int20  DECLARE @ElementCount int21  DECLARE @ElementIndex int22  DECLARE @XUser nvarchar(64) = object_name(@@procid)23  DECLARE @Xdate datetime = getutcdate()24  SET XACT_ABORT OFF25  BEGIN TRY26    IF NOT EXISTS(27      SELECT TOP 1 128      FROM QBM_VSchemaColumns c29      WHERE30        c.TABLE_NAME = @TableName AND c.COLUMN_NAME = @ColumnName)31    BEGIN32      SELECT @Cmd = '33	alter table ' + @TableName + ' add ' + @ColumnName + ' ' + @TypeDef34      IF @DebugSwitch > 035      BEGIN36        print @cmd37      END38      EXEC sp_executesql @cmd39    END40    SELECT TOP 1 @datatype = c.DATA_TYPE41    FROM INFORMATION_SCHEMA.COLUMNS c42    WHERE43      c.TABLE_NAME = @TableName AND c.COLUMN_NAME = @ColumnName44    IF @datatype IN('bit',45    'int',46    'bigint',47    'float',48    'real')49    BEGIN50      SELECT51        @Cmd = CONCAT('52	update ',53        @TableName,54        ' set ',55        @ColumnName,56        ' = 0  ',57        ' where ',58        @ColumnName,59        ' is null')60      IF @DebugSwitch > 061      BEGIN62        print @cmd63      END64      EXEC sp_executesql @cmd65      IF EXISTS(66        SELECT TOP 1 167        FROM INFORMATION_SCHEMA.COLUMNS c68        WHERE69          c.TABLE_NAME = @TableName AND c.COLUMN_NAME = @ColumnName AND c.COLUMN_DEFAULT IS NULL)70      BEGIN71        SELECT72          @cmd = CONCAT('sp_bindefault ''DezimalNULL'', ''',73          @TableName,74          '.',75          @ColumnName,76          '''')77        EXEC sp_executesql @cmd78      END79    END80    IF @UID_DialogColumn IS NULL81    BEGIN82      GOTO endLabel83    END84    IF NOT EXISTS(85      SELECT TOP 1 186      FROM sys.tables t87      WHERE88        t.name = 'DialogColumn')89    BEGIN90      GOTO endLabel91    END92    IF @UID_DialogTable IS NULL93    BEGIN94      SELECT TOP 1 @UID_DialogTableIntern = t.UID_DialogTable95      FROM DialogTable t96      WHERE97        t.TableName = @TableName98    END99    ELSE100    BEGIN101      SELECT @UID_DialogTableIntern = @UID_DialogTable102    END103    IF @UID_DialogTableIntern IS NULL104    BEGIN105      GOTO EndLabel106    END107    SELECT @OwnerDB = dbo.QBM_FGIDBOwner()108    SELECT @OwnerColumn = dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogColumn)109    SELECT @cmd = 'select name from sys.views where name like ''___[_]VElementsToKeep'''110    INSERT INTO @ViewsWithElementsToKeep(ObjectKey1)111    EXEC sp_executesql @cmd112    SELECT @ElementCount = @@ROWCOUNT113    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1114    SELECT @ElementLast = @@IDENTITY115    SELECT @ElementsToKeep = 0116    WHILE @ElementIndex <= @ElementLast AND @ElementsToKeep = 0117    BEGIN118      SELECT TOP 1 @ViewName = v.ObjectKey1119      FROM @ViewsWithElementsToKeep v120      WHERE121        v.ElementIndex = @ElementIndex122      SELECT @ElementsToKeep = 0123      SELECT124        @cmd = 'select top 1 1 from ' + @ViewName + ' where SourceModule = ''' + @OwnerColumn + ''''125      EXEC sp_executesql @cmd126      SELECT @ElementsToKeep = @@ROWCOUNT127      SELECT @ElementIndex += 1128    END129    IF @OwnerColumn <> 'CCC' AND @OwnerColumn <> @OwnerDB AND @ElementsToKeep = 0130    BEGIN131      GOTO EndLabel132    END133    IF @UID_DialogTable > ' ' AND NOT EXISTS(134      SELECT TOP 1 1135      FROM DialogTable t136      WHERE137        t.TableName = @TableName)138    BEGIN139      INSERT INTO DialogTable(TableName,140      UID_DialogTable,141      TableType,142      XObjectKey,143      XDateInserted,144      XDateUpdated,145      XUserInserted,146      XUserUpdated)147      VALUES(@TableName,148      @UID_DialogTableIntern,149      'T',150      dbo.QBM_FCVElementToObjectKey1('DialogTable', '', @UID_DialogTableIntern),151      @Xdate,152      @Xdate,153      @XUser,154      @XUser)155    END156    IF NOT EXISTS(157      SELECT TOP 1 1158      FROM DialogColumn c159      WHERE160        c.UID_DialogColumn = @UID_DialogColumn)161    BEGIN162      INSERT INTO DialogColumn(UID_DialogColumn,163      UID_DialogTable,164      XObjectKey,165      ColumnName)166      SELECT167        @UID_DialogColumn,168        @UID_DialogTableIntern,169        dbo.QBM_FCVElementToObjectKey1('DialogColumn',170        '',171        @UID_DialogColumn),172        @ColumnName173    END174    UPDATE DialogColumn175    SET SchemaDataType = x.DATA_TYPE,176    SchemaDataLen = x.CHARACTER_MAXIMUM_LENGTH,177    Datatype = dbo.QBM_FCVSchemaDatatypeToIntern(x.DATA_TYPE,178    x.CHARACTER_MAXIMUM_LENGTH),179    XUserInserted = 'QBM_PColumnAdd',180    XUserUpdated = 'QBM_PColumnAdd',181    XDateInserted = GETUTCDATE(),182    XDateUpdated = GETUTCDATE()183    FROM DialogColumn c184    JOIN DialogTable t185      ON c.UID_DialogTable = t.UID_DialogTable186    JOIN INFORMATION_SCHEMA.COLUMNS x187      ON t.TableName = x.TABLE_NAME AND c.ColumnName = x.COLUMN_NAME188    WHERE189      (isnull(SchemaDataType, '') <> x.DATA_TYPE OR isnull(schemaDataLen, 0) <> isnull(x.CHARACTER_MAXIMUM_LENGTH,190    0) OR isnull(Datatype, -1) = dbo.QBM_FCVSchemaDatatypeToIntern(x.DATA_TYPE, x.CHARACTER_MAXIMUM_LENGTH)) AND c.UID_DialogColumn = @UID_DialogColumn191  END TRY192  BEGIN CATCH193    EXEC QBM_PSessionErrorAdd DEFAULT194    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()195    RAISERROR(@Rethrow,196    18,197    1)198      WITH NOWAIT199  END CATCH200  endLabel:201  RETURN202END

Open raw exported source

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(383) declare @datatype nvarchar(64) declare @OwnerDB varchar(3) declare @OwnerColumn varchar(3) declare @ElementsToKeep int declare @ViewsWithElementsToKeep4 QBM_YCursorBuffer declare @ViewName varchar(30) declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare @XUser nvarchar(645) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY if not exists (select top 1 1 from QBM_VSchemaColumns c where6 c.TABLE_NAME = @TableName and c.COLUMN_NAME = @ColumnName ) begin select @Cmd = '7	alter table ' + @TableName + ' add ' + @ColumnName + ' ' + @TypeDef8 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_NAME9 = @TableName and c.COLUMN_NAME = @ColumnName if @datatype in ('bit', 'int', 'bigint', 'float', 'real') begin select @Cmd = concat('10	update ' , @TableName11 , ' set ' , @ColumnName , ' = 0  '  ,' where ' , @ColumnName , ' is null' ) if @DebugSwitch > 0 begin print @cmd end exec sp_executesql @cmd  if exists12 (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 ) begin13 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_DialogTable15 is null  begin select top 1 @UID_DialogTableIntern = t.UID_DialogTable from DialogTable t where t.TableName = @TableName end else begin select @UID_DialogTableIntern16 = @UID_DialogTable end if @UID_DialogTableIntern is null begin    goto EndLabel   end   select @OwnerDB = dbo.QBM_FGIDBOwner() select @OwnerColumn = dbo.QBM_FCVGUIDToModuleOwner17(@UID_DialogColumn) select @cmd = 'select name from sys.views where name like ''___[_]VElementsToKeep''' insert into @ViewsWithElementsToKeep (ObjectKey118) exec sp_executesql @cmd select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY select19 @ElementsToKeep = 0 while @ElementIndex <= @ElementLast and @ElementsToKeep = 0 begin select top 1 @ViewName = v.ObjectKey1 from @ViewsWithElementsToKeep20 v where v.ElementIndex = @ElementIndex select @ElementsToKeep = 0 select @cmd = 'select top 1 1 from ' + @ViewName + ' where SourceModule = ''' + @OwnerColumn21 + '''' exec sp_executesql @cmd select @ElementsToKeep = @@ROWCOUNT select @ElementIndex += 1 end if @OwnerColumn <> 'CCC' and @OwnerColumn <> @OwnerDB22 and @ElementsToKeep = 0 begin  goto EndLabel end if @UID_DialogTable > ' '  and not exists (select top 1 1 from DialogTable t where t.TableName = @TableName23 ) begin insert into DialogTable( TableName, UID_DialogTable, TableType, XObjectKey , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated ) values24 ( @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_DialogColumn26, UID_DialogTable, XObjectKey , ColumnName) select @UID_DialogColumn, @UID_DialogTableIntern, dbo.QBM_FCVElementToObjectKey1('DialogColumn', '', @UID_DialogColumn27) , @ColumnName end update DialogColumn set SchemaDataType = x.DATA_TYPE , SchemaDataLen = x.CHARACTER_MAXIMUM_LENGTH , Datatype = dbo.QBM_FCVSchemaDatatypeToIntern28(x.DATA_TYPE, x.CHARACTER_MAXIMUM_LENGTH) , XUserInserted = 'QBM_PColumnAdd' , XUserUpdated = 'QBM_PColumnAdd' , XDateInserted = GETUTCDATE() , XDateUpdated29 = GETUTCDATE() from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable join INFORMATION_SCHEMA.COLUMNS x on t.TableName = x.TABLE_NAME30 and c.ColumnName = x.COLUMN_NAME where ( isnull(SchemaDataType, '') <> x.DATA_TYPE or isnull(schemaDataLen, 0) <> isnull(x.CHARACTER_MAXIMUM_LENGTH, 031) or isnull(Datatype, -1) = dbo.QBM_FCVSchemaDatatypeToIntern(x.DATA_TYPE, x.CHARACTER_MAXIMUM_LENGTH) ) and c.UID_DialogColumn = @UID_DialogColumn END32 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT33 END CATCH endLabel: return end 34