Back to OIM Explorer

dbo.QBM_FSQTriggerWatchColumn

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 4.476 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVStringToIndent source text reference
references source dbo.QBM_FSQCVColumnToBuffer source text reference
references source dbo.QBM_FSQIsNullClauseCmp source text reference
references source dbo.QBM_FSQTableJoin source text reference
references source dbo.QBM_FSQTriggerWatchDisplay source text reference
references source dbo.QBM_FTColumnsToWatch source text reference

References

Referenced By

dbo.QBM_FSQTriggerWatchTable

Complete Source

1CREATE FUNCTION dbo.QBM_FSQTriggerWatchColumn(2  @TableName nvarchar(64),3  @operation nvarchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @columnname varchar(30)9  DECLARE @TableNameBase varchar(30)10  DECLARE @realColumnname varchar(30)11  DECLARE @uid_dialogcolumn varchar(38)12  DECLARE @tableview varchar(30)13  DECLARE @whereclause nvarchar(max)14  DECLARE @IsAlreadyInsertClause BIT = 015  DECLARE @IsAlreadyInsertValue BIT = 016  DECLARE @columnname_alt varchar(30) = ''17  DECLARE @erg nvarchar(max) = N ''18  DECLARE @SchemaDatatype varchar(128)19  DECLARE @OperationForColumns varchar(16)20  DECLARE @elements TABLE(ElementIndex int identity NOT NULL,21  columnname varchar(30) collate database_default,22  uid_dialogcolumn varchar(38) collate database_default,23  tableview varchar(30) collate database_default,24  realColumnname varchar(30) collate database_default,25  TableNameBase varchar(30) collate database_default,26  SchemaDatatype varchar(128) collate database_default)27  DECLARE @ElementCount int28  DECLARE @ElementIndex int29  IF @operation = 'UpdateDelete'30  BEGIN31    SELECT @OperationForColumns = 'Delete'32  END33  ELSE34  BEGIN35    SELECT @OperationForColumns = @operation36  END37  INSERT INTO @elements(columnname,38  uid_dialogcolumn,39  tableview,40  realColumnname,41  TableNameBase,42  SchemaDatatype)43  SELECT44    wg.columnname,45    rtrim(c.uid_dialogColumn),46    wg.TableNameView,47    wg.ColumnNameView,48    isnull(bt.TableName,49    t.TableName),50    c.SchemaDataType51  FROM dbo.QBM_FTColumnsToWatch(@TableName,52  @OperationForColumns) wg53  JOIN dialogColumn c54    WITH(readpast)55    ON wg.columnnameView = c.columnname56  JOIN DialogTable t57    WITH(readpast)58    ON wg.TableNameView = t.TableName AND c.UID_DialogTable = t.UID_DialogTable59  LEFT60  OUTER61  JOIN DialogTable bt62    WITH(readpast)63    ON t.UID_DialogTableBase = bt.UID_DialogTable64  ORDER BY wg.ColumnName,65  wg.TableNameView66  SELECT @ElementCount = @@ROWCOUNT67  SELECT @ElementIndex = 168  WHILE @ElementIndex <= @ElementCount69  BEGIN70    SELECT @whereclause = N ''71    SELECT72      TOP 1 @columnname = e.columnname,73      @uid_dialogcolumn = e.uid_dialogcolumn,74      @tableview = e.tableview,75      @realColumnname = e.realColumnname,76      @TableNameBase = e.TableNameBase,77      @SchemaDatatype = e.SchemaDatatype78    FROM @elements e79    WHERE80      e.ElementIndex = @ElementIndex81    IF @operation = 'update' AND @columnname <> @Columnname_alt AND @ElementIndex > 182    BEGIN83      SELECT @erg = @erg + N '84		 end -- if update(' + @columnname_alt + ')'85    END86    IF @operation = 'update' AND @columnname <> @Columnname_alt87    BEGIN88      SELECT @erg = @erg + N '89		if update(' + @columnname + ')90		 begin'91      SELECT @IsAlreadyInsertClause = 092    END93    IF @IsAlreadyInsertClause = 094    BEGIN95      SELECT96        @erg = @erg + N '97			insert into @DialogWatchProperty (UID_DialogColumn, ObjectKeyOfRow98											   , ContentShort, HasContentFull, ContentFull, DisplayValue)'99      SELECT @IsAlreadyInsertClause = 1100      SELECT @IsAlreadyInsertValue = 0101    END102    IF @IsAlreadyInsertValue = 1103    BEGIN104      SELECT @erg = @erg + N '105			  union all'106    END107    SELECT108      @erg = @erg + N '109				select ''' + @uid_dialogColumn + N ''', d.XObjectKey,  -- ' + @realColumnname + N '110	'111    SELECT @IsAlreadyInsertValue = 1112    SELECT113      @erg = @erg + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCVColumnToBuffer(@TableNameBase, @columnname,114      'd'),115      8)116    SELECT117      @erg = @erg + N ', ' + dbo.QBM_FSQTriggerWatchDisplay(@TableName,118      N 'd')119    SELECT @erg = @erg + N '120					from deleted d '121    IF @operation IN('update',122    'updateDelete')123    BEGIN124      SELECT125        @erg = @erg + N ' join ' + @TableName + N ' t on ' + dbo.QBM_FSQTableJoin(@TableName,126        'd',127        't')128    END129    IF @operation = 'UpdateDelete'130    BEGIN131      SELECT @whereclause = N '132					where d.XOrigin > 0 and t.XOrigin = 0'133    END134    IF @operation = 'update'135    BEGIN136      SELECT137        @whereclause = N '138				where ' + dbo.QBM_FSQIsNullClauseCmp(@TableNameBase,139        @columnname,140        'd') + ' <> ' + dbo.QBM_FSQIsNullClauseCmp(@TableNameBase,141        @columnname,142        't')143      IF @SchemaDatatype LIKE '%char%'144      BEGIN145        SELECT146          @whereclause = @whereclause + ' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/ '147      END148    END149    IF @TableName <> @tableview150    BEGIN151      IF len(@whereclause) > 0152      BEGIN153        SELECT @whereclause = @whereclause + N '154						and'155      END156      ELSE157      BEGIN158        SELECT @whereclause = N '159					where'160      END161      SELECT162        @whereclause = @whereclause + N ' d.XObjectKey like ''<Key><T>' + @tableview + '</T>%'''163    END164    SELECT @erg = @erg + @whereclause165    SELECT @Columnname_alt = @columnname166    IF @operation = 'update' AND @ElementIndex = @ElementCount167    BEGIN168      SELECT @erg = @erg + N '169		 end -- if update(' + @columnname_alt + ')'170    END171    SELECT @ElementIndex += 1172  END173  RETURN(@erg)174END

Open raw exported source

1      create   function dbo.QBM_FSQTriggerWatchColumn(@TableName nvarchar(64), @operation nvarchar(16) ) returns nvarchar(max) as begin declare 2@columnname varchar(30) declare @TableNameBase varchar(30) declare @realColumnname varchar(30)   declare @uid_dialogcolumn varchar(38) declare @tableview3 varchar(30) declare @whereclause nvarchar(max) declare @IsAlreadyInsertClause bit = 0 declare @IsAlreadyInsertValue bit = 0 declare @columnname_alt varchar4(30) = '' declare @erg nvarchar(max) = N'' declare @SchemaDatatype varchar(128) declare @OperationForColumns varchar(16) declare @elements table (ElementIndex5 int identity NOT NULL , columnname varchar(30) collate database_default , uid_dialogcolumn varchar(38) collate database_default , tableview varchar(306) collate database_default , realColumnname varchar(30) collate database_default , TableNameBase varchar(30) collate database_default , SchemaDatatype 7varchar(128) collate database_default ) declare @ElementCount int declare @ElementIndex int if @operation = 'UpdateDelete' begin select @OperationForColumns8 = 'Delete' end else begin select @OperationForColumns = @operation end insert into @elements(columnname , uid_dialogcolumn , tableview , realColumnname9 , TableNameBase , SchemaDatatype ) select wg.columnname , rtrim(c.uid_dialogColumn), wg.TableNameView, wg.ColumnNameView , isnull(bt.TableName, t.TableName10) , c.SchemaDataType from dbo.QBM_FTColumnsToWatch(@TableName , @OperationForColumns) wg join dialogColumn c with (readpast) on wg.columnnameView = c.columnname11 join DialogTable t with (readpast) on wg.TableNameView = t.TableName and c.UID_DialogTable = t.UID_DialogTable left outer join DialogTable bt with (readpast12) on t.UID_DialogTableBase = bt.UID_DialogTable order by wg.ColumnName, wg.TableNameView select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while13 @ElementIndex <= @ElementCount begin select @whereclause = N'' select top 1 @columnname = e.columnname , @uid_dialogcolumn = e.uid_dialogcolumn , @tableview14 = e.tableview , @realColumnname = e.realColumnname , @TableNameBase = e.TableNameBase , @SchemaDatatype = e.SchemaDatatype from @elements e where e.ElementIndex15 = @ElementIndex     if @operation = 'update' and @columnname <> @Columnname_alt and @ElementIndex > 1 begin select @erg = @erg + N'16		 end -- if update('17 + @columnname_alt + ')' end if @operation = 'update' and @columnname <> @Columnname_alt begin select @erg = @erg + N'18		if update(' + @columnname + ')19		 begin'20 select @IsAlreadyInsertClause = 0 end if @IsAlreadyInsertClause = 0 begin select @erg = @erg + N'21			insert into @DialogWatchProperty (UID_DialogColumn, ObjectKeyOfRow22											   , ContentShort, HasContentFull, ContentFull, DisplayValue)'23 select @IsAlreadyInsertClause = 1 select @IsAlreadyInsertValue = 0 end if @IsAlreadyInsertValue = 1 begin select @erg = @erg + N'24			  union all' end25 select @erg = @erg + N'26				select ''' + @uid_dialogColumn + N''', d.XObjectKey,  -- ' + @realColumnname + N'27	' select @IsAlreadyInsertValue = 1 select28 @erg = @erg + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQCVColumnToBuffer(@TableNameBase, @columnname, 'd'), 8) select @erg = @erg + N', ' + dbo.QBM_FSQTriggerWatchDisplay29( @TableName , N'd') select @erg = @erg + N'30					from deleted d ' if @operation in( 'update' , 'updateDelete') begin select @erg = @erg + N' join ' +31 @TableName + N' t on ' + dbo.QBM_FSQTableJoin(@TableName , 'd', 't') end if @operation = 'UpdateDelete' begin select @whereclause = N'32					where d.XOrigin > 0 and t.XOrigin = 0'33 end   if @operation = 'update' begin select @whereclause = N'34				where ' + dbo.QBM_FSQIsNullClauseCmp(@TableNameBase, @columnname, 'd') + ' <> ' + dbo.QBM_FSQIsNullClauseCmp35(@TableNameBase, @columnname, 't')  if @SchemaDatatype like '%char%' begin select @whereclause = @whereclause + ' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/ '36 end  end   if @TableName <> @tableview begin if len(@whereclause) > 0 begin select @whereclause = @whereclause + N'37						and' end else begin select 38@whereclause = N'39					where' end select @whereclause = @whereclause + N' d.XObjectKey like ''<Key><T>' + @tableview + '</T>%''' end select @erg = @erg40 + @whereclause select @Columnname_alt = @columnname   if @operation = 'update' and @ElementIndex = @ElementCount begin select @erg = @erg + N'41		 end -- if update('42 + @columnname_alt + ')' end select @ElementIndex += 1 end  return (@erg) end 43