Back to OIM Explorer

dbo.QER_FTDynGroupColForImmediate

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 4.248 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_FCVStringToList source text reference
references source dbo.QBM_FGITableName source text reference
references source dbo.QBM_FCVStringToListSQLMo_int source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE FUNCTION dbo.QER_FTDynGroupColForImmediate(2  @UID_DynamicGroup varchar(38)3) RETURNS @erg TABLE(TableName varchar(30) collate database_default,4ColumnName varchar(30) collate database_default,5CaptureInsert BIT,6CaptureUpdate BIT,7CaptureDelete BIT,8UID_DialogTable varchar(38) collate database_default,9UID_DialogColumn varchar(38) collate database_default10)11AS12BEGIN13  DECLARE @TablenameObjectClass varchar(30)14  DECLARE @whereClause nvarchar(max)15  DECLARE @AnalysePrefix nvarchar(max)16  DECLARE @elemente TABLE(TableName varchar(30) collate database_default,17  ColumnName varchar(30) collate database_default,18  WatchInsertDelete varchar(30) collate database_default,19  WatchUpdate varchar(30) collate database_default,20  RelationInfo varchar(30) collate database_default)21  DECLARE @Morpheme QBM_YParameterList22  DECLARE @UID_DialogTableObjectClass varchar(38)23  SELECT24    TOP 1 @whereClause = g.WhereClause,25    @UID_DialogTableObjectClass = g.UID_DialogTableObjectClass,26    @TablenameObjectClass = dbo.QBM_FGITableName(g.UID_DialogTableObjectClass)27  FROM DynamicGroup g28  WHERE29    g.UID_DynamicGroup = @UID_DynamicGroup30  SELECT31    @AnalysePrefix = CONCAT('select * from ',32    @TablenameObjectClass,33    ' where ')34  INSERT INTO @Morpheme(Parameter1,35  Parameter2)36  SELECT CASE37  WHEN mo.Morphem LIKE '%.%' THEN38  mo.p239  ELSE mo.Morphem40  END,41  mo.MorphemSubType42  FROM dbo.QBM_FCVStringToListSQLMo_int(@AnalysePrefix + @whereClause,43  0,44  1,45  0) mo46  WHERE47    mo.MorphemType = 'SchemaObject' AND mo.MorphemSubType IN('Table',48  'Column',49  'view')50  UNION51  SELECT52    @TablenameObjectClass,53    'Table'54  UNION55  SELECT56    c.PKName1,57    'Column'58  FROM DialogTable c59    WITH(readpast)60  WHERE61    c.UID_DialogTable = @UID_DialogTableObjectClass62  INSERT INTO @Morpheme(Parameter1,63  Parameter2)64  SELECT65    c.ColumnName,66    'Column'67  FROM @Morpheme mo68  JOIN DialogTable t69    WITH(readpast)70    ON mo.Parameter1 = t.TableName71  JOIN DialogColumn c72    ON t.UID_DialogTable = c.UID_DialogTable AND c.IsPKMember = 173  WHERE74    mo.Parameter2 IN('Table',75  'View') AND NOT EXISTS(76  SELECT TOP 1 177  FROM @Morpheme e78  WHERE79    e.Parameter1 = c.ColumnName AND e.Parameter2 = 'Column')80  INSERT INTO @elemente(Tablename,81  Columnname,82  WatchInsertDelete,83  WatchUpdate)84  SELECT85    DISTINCT t.TableName,86    c.ColumnName,87  CASE88    WHEN t.TableName <> @TablenameObjectClass AND c.IsPKMember = 0 THEN89  'no'90    WHEN t.TableName <> @TablenameObjectClass AND c.IsPKMember = 1 THEN91  'Full'92    WHEN t.TableName = @TablenameObjectClass AND c.IsPKMember = 0 THEN93  'no'94  ELSE 'Detail'95  END AS WatchInsertDelete,96  CASE97    WHEN t.TableName <> @TablenameObjectClass AND c.IsPKMember = 0 THEN98  'Full'99    WHEN t.TableName <> @TablenameObjectClass AND c.IsPKMember = 1 THEN100  'no'101    WHEN t.TableName = @TablenameObjectClass AND c.IsPKMember = 0 THEN102  'Detail'103  ELSE 'no'104  END AS WatchUpdate105  FROM @Morpheme m106  JOIN DialogTable t107    WITH(readpast)108    ON (m.Parameter1 = t.TableName AND m.Parameter2 IN('Table', 'View') AND(EXISTS(109  SELECT TOP 1 1110  FROM QBM_VQBMRelation r111  WHERE112    @TablenameObjectClass IN(r.ParentTable, r.ChildTable)))) OR t.TableName = @TablenameObjectClass113  JOIN DialogColumn c114    ON t.UID_DialogTable = c.UID_DialogTable115  JOIN @Morpheme mc116    ON mc.Parameter1 = c.ColumnName AND mc.Parameter2 = 'column'117  INSERT INTO @erg(TableName,118  ColumnName,119  CaptureInsert,120  CaptureDelete,121  CaptureUpdate,122  UID_DialogTable,123  UID_DialogColumn)124  SELECT125    y.TableName,126    y.ColumnName,127    sign(len(REPLACE(y.WatchInsertDelete, 'no', ''))),128    sign(len(REPLACE(y.WatchInsertDelete, 'no', ''))),129    sign(len(REPLACE(y.WatchUpdate, 'no', ''))),130    t.UID_DialogTable,131    c.UID_DialogColumn132  FROM(133  SELECT134    x.TableName,135    x.ColumnName,136    MIN(x.WatchInsertDelete) AS WatchInsertDelete,137    MIN(x.WatchUpdate) AS WatchUpdate138  FROM(139  SELECT140    TableName, Columnname, WatchInsertDelete, WatchUpdate141  FROM @elemente tc142  UNION143  SELECT144    r.ChildTable AS TableToWatch, r.ChildColumn, 'Detail', 'Detail'145  FROM QBM_VQBMRelationALL r146  JOIN @elemente tc147    ON tc.TableName = r.ChildTable148  WHERE149    r.UID_DialogTableParent = @UID_DialogTableObjectClass AND r.UID_DialogTableChild <> @UID_DialogTableObjectClass) AS x150  GROUP BY x.TableName,151  x.ColumnName) AS y152  JOIN DialogTable t153    WITH(readpast)154    ON t.TableName = y.TableName155  JOIN DialogColumn c156    ON t.UID_DialogTable = c.UID_DialogTable AND c.ColumnName = y.ColumnName endLabel:157  RETURN158END

Open raw exported source

1create function dbo.QER_FTDynGroupColForImmediate(@UID_DynamicGroup varchar(38) ) returns @erg table ( TableName varchar(30) collate database_default ,2 ColumnName varchar(30) collate database_default , CaptureInsert bit , CaptureUpdate bit , CaptureDelete bit , UID_DialogTable varchar(38) collate database_default3 , UID_DialogColumn varchar(38) collate database_default ) as begin declare @TablenameObjectClass varchar(30) declare @whereClause nvarchar(max) declare4 @AnalysePrefix nvarchar(max) declare @elemente table( TableName varchar(30) collate database_default , ColumnName varchar(30) collate database_default5 , WatchInsertDelete varchar(30) collate database_default , WatchUpdate varchar(30) collate database_default , RelationInfo varchar(30) collate database_default6 ) declare @Morpheme QBM_YParameterList declare @UID_DialogTableObjectClass varchar(38) select top 1 @whereClause = g.WhereClause , @UID_DialogTableObjectClass7 = g.UID_DialogTableObjectClass , @TablenameObjectClass = dbo.QBM_FGITableName(g.UID_DialogTableObjectClass) from DynamicGroup g where g.UID_DynamicGroup8 = @UID_DynamicGroup select @AnalysePrefix = concat('select * from ', @TablenameObjectClass ,' where ')  insert into @Morpheme(Parameter1  , Parameter29 ) select case when mo.Morphem like '%.%' then mo.p2 else mo.Morphem end ,  mo.MorphemSubType from dbo.QBM_FCVStringToListSQLMo_int(@AnalysePrefix + @whereClause10, 0,1,0) mo where mo.MorphemType = 'SchemaObject' and mo.MorphemSubType in ( 'Table', 'Column', 'view') union select @TablenameObjectClass, 'Table' union11 select c.PKName1, 'Column' from DialogTable c with (readpast) where c.UID_DialogTable = @UID_DialogTableObjectClass insert into @Morpheme(Parameter1, 12Parameter2) select c.ColumnName, 'Column' from @Morpheme mo join DialogTable t with (readpast) on mo.Parameter1 = t.TableName join DialogColumn c on t.UID_DialogTable13 = c.UID_DialogTable and c.IsPKMember = 1 where mo.Parameter2 in ('Table', 'View') and not exists (Select top 1 1 from @Morpheme e where e.Parameter1 =14 c.ColumnName and e.Parameter2 = 'Column' )  insert into @elemente (Tablename , Columnname , WatchInsertDelete, WatchUpdate) select distinct t.TableName15, c.ColumnName, case when t.TableName <> @TablenameObjectClass and c.IsPKMember = 0 then 'no'  when t.TableName <> @TablenameObjectClass and c.IsPKMember16 = 1 then 'Full' when t.TableName = @TablenameObjectClass and c.IsPKMember = 0 then 'no'  else 'Detail' end as WatchInsertDelete , case when t.TableName17 <> @TablenameObjectClass and c.IsPKMember = 0 then 'Full'  when t.TableName <> @TablenameObjectClass and c.IsPKMember = 1 then 'no' when t.TableName =18 @TablenameObjectClass and c.IsPKMember = 0 then 'Detail' else 'no' end as WatchUpdate from @Morpheme m join DialogTable t with (readpast) on (m.Parameter119  = t.TableName and m.Parameter2 in ('Table', 'View') and (exists (select top 1 1 from QBM_VQBMRelation r where @TablenameObjectClass in (r.ParentTable20, r.ChildTable) ) ) ) or t.TableName = @TablenameObjectClass  join DialogColumn c on t.UID_DialogTable = c.UID_DialogTable join @Morpheme mc on mc.Parameter121  = c.ColumnName and mc.Parameter2 = 'column' insert into @erg(TableName, ColumnName, CaptureInsert, CaptureDelete, CaptureUpdate, UID_DialogTable, UID_DialogColumn22) select y.TableName, y.ColumnName, sign(len(REPLACE(y.WatchInsertDelete, 'no', ''))) , sign(len(REPLACE(y.WatchInsertDelete, 'no', ''))) , sign(len(REPLACE23(y.WatchUpdate, 'no', ''))) , t.UID_DialogTable, c.UID_DialogColumn from ( select x.TableName, x.ColumnName  , MIN(x.WatchInsertDelete) as WatchInsertDelete24, MIN(x.WatchUpdate) as WatchUpdate from ( select TableName, Columnname , WatchInsertDelete, WatchUpdate from @elemente tc union select r.ChildTable as25 TableToWatch, r.ChildColumn , 'Detail', 'Detail' from QBM_VQBMRelationALL r  join @elemente tc on tc.TableName = r.ChildTable where r.UID_DialogTableParent26 = @UID_DialogTableObjectClass and r.UID_DialogTableChild <> @UID_DialogTableObjectClass ) as x group by x.TableName, x.ColumnName ) as y join DialogTable27 t with (readpast) on t.TableName = y.TableName join DialogColumn c on t.UID_DialogTable = c.UID_DialogTable and c.ColumnName = y.ColumnName endLabel: 28return end 29