Back to OIM Explorer

dbo.QBM_FTDialogProcessSelect

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 3.906 characters

Interpretation

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

Relations

No extracted relations.

Typed Edges

No typed edges extracted for this source.

References

No direct source references extracted.

Referenced By

No direct source references extracted.

Complete Source

1CREATE FUNCTION dbo.QBM_FTDialogProcessSelect(2  @ObjectKey varchar(138),3  @XUserInserted nvarchar(64),4  @ChildrenList varchar(max),5  @IncludeSubstitution BIT6) RETURNS @erg TABLE(BasisObjectType nvarchar(32) collate database_default,7CustomComment nvarchar(256) collate database_default,8GenProcID varchar(38) collate database_default,9ObjectKey varchar(138) collate database_default,10DisplayName nvarchar(1024) collate database_default,11ProcessState nchar(1) collate database_default,12XUserInserted nvarchar(64) collate database_default,13XUserUpdated nvarchar(64) collate database_default,14XDateInserted datetime,15XDateUpdated datetime,16Origin int17)18  WITH schemabinding19AS20BEGIN21  DECLARE @Work dbo.QBM_YCursorBuffer22  DECLARE @Children dbo.QBM_YParameterList23  IF @ChildrenList > ' '24  BEGIN25    INSERT INTO @Children(Parameter1)26    SELECT y.Value27    FROM string_split(@ChildrenList,28    char(7)) AS y29    WHERE30      y.value > ' '31    IF @@ROWCOUNT > 032    BEGIN33      INSERT INTO @Work(UID1,34      Int1)35      SELECT36        p.GenProcID,37        17 AS Origin38      FROM dbo.DialogProcess p39        WITH(readpast)40      JOIN @Children l41        ON p.ObjectKey = l.Parameter142      UNION all43      SELECT44        p.GenProcID,45        18 AS Origin46      FROM dbo.DialogProcessChain p47        WITH(readpast)48      JOIN @Children l49        ON p.ObjectKey = l.Parameter150    END51  END52  IF @ObjectKey > ' '53  BEGIN54    INSERT INTO @Work(UID1,55    Int1)56    SELECT57      p.GenProcID,58      1 AS Origin59    FROM dbo.DialogProcess p60      WITH(readpast)61    WHERE62      p.ObjectKey = @ObjectKey63    UNION all64    SELECT65      p.GenProcID,66      2 AS Origin67    FROM dbo.DialogProcessChain p68      WITH(readpast)69    WHERE70      p.ObjectKey = @ObjectKey71  END72  IF @XUserInserted > ' '73  BEGIN74    INSERT INTO @Work(UID1,75    Int1)76    SELECT77      p.GenProcID,78      1 AS Origin79    FROM dbo.DialogProcess p80      WITH(readpast)81    WHERE82      p.XUserInserted = @XUserInserted83    UNION all84    SELECT85      p.GenProcID,86      2 AS Origin87    FROM dbo.DialogProcessChain p88      WITH(readpast)89    WHERE90      p.XUserInserted = @XUserInserted91  END92  IF @ObjectKey > ' ' AND @IncludeSubstitution = 193  BEGIN94    INSERT INTO @Work(UID1,95    Int1)96    SELECT97      s.GenProcIDNew AS GenProcID,98      4 AS Origin99    FROM dbo.DialogProcessSubstitute s100      WITH(readpast)101    WHERE102      EXISTS(103    SELECT TOP 1 1104    FROM dbo.DialogProcess p105      WITH(readpast)106    WHERE107      p.ObjectKey = @ObjectKey AND p.GenProcID = s.GenProcIDOrigin) AND s.ReadyForDeleteOrExport = 0108    UNION all109    SELECT110      s.GenProcIDOrigin AS GenProcID,111      8 AS Origin112    FROM dbo.DialogProcessSubstitute s113      WITH(readpast)114    WHERE115      EXISTS(116    SELECT TOP 1 1117    FROM dbo.DialogProcess p118      WITH(readpast)119    WHERE120      p.ObjectKey = @ObjectKey AND p.genprocId = s.GenProcIDNew) AND s.ReadyForDeleteOrExport = 0121  END122  IF @XUserInserted > ' ' AND @IncludeSubstitution = 1123  BEGIN124    INSERT INTO @Work(UID1,125    Int1)126    SELECT127      s.GenProcIDNew AS GenProcID,128      4 AS Origin129    FROM dbo.DialogProcessSubstitute s130      WITH(readpast)131    WHERE132      EXISTS(133    SELECT TOP 1 1134    FROM dbo.DialogProcess p135      WITH(readpast)136    WHERE137      p.XUserInserted = @XUserInserted AND p.GenProcID = s.GenProcIDOrigin) AND s.ReadyForDeleteOrExport = 0138    UNION all139    SELECT140      s.GenProcIDOrigin AS GenProcID,141      8 AS Origin142    FROM dbo.DialogProcessSubstitute s143      WITH(readpast)144    WHERE145      EXISTS(146    SELECT TOP 1 1147    FROM dbo.DialogProcess p148      WITH(readpast)149    WHERE150      p.XUserInserted = @XUserInserted AND p.genprocId = s.GenProcIDNew) AND s.ReadyForDeleteOrExport = 0151  END152  INSERT INTO @erg(BasisObjectType,153  CustomComment,154  GenProcID,155  ObjectKey,156  DisplayName,157  ProcessState,158  XUserInserted,159  XDateInserted,160  XDateUpdated,161  Origin,162  XUserUpdated)163  SELECT164    d.BasisObjectType,165    d.CustomComment,166    d.GenProcID,167    d.ObjectKey,168    d.DisplayName,169    d.ProcessState,170    d.XUserInserted,171    d.XDateInserted,172    d.XDateUpdated,173    x.Origin,174    d.XUserUpdated175  FROM dbo.DialogProcess d176    WITH(readpast)177  JOIN(178  SELECT179    w.UID1,180    MAX(w.Int1 & 1) + MAX(w.Int1 & 2) +MAX(w.Int1 & 4) + MAX(w.Int1 & 8) + MAX(w.Int1 & 16) AS Origin181  FROM @Work w182  GROUP BY w.UID1) AS x183    ON d.GenProcID = x.UID1184  WHERE185    EXISTS(186  SELECT TOP 1 1187  FROM dbo.DialogProcessChain pc188    WITH(readpast)189  WHERE190    pc.GenProcID = d.GenProcID)191  RETURN192END

Open raw exported source

1create function dbo.QBM_FTDialogProcessSelect(@ObjectKey varchar(138)  , @XUserInserted nvarchar(64)  , @ChildrenList varchar(max)  , @IncludeSubstitution2 bit ) returns @erg table ( BasisObjectType nvarchar(32) collate database_default , CustomComment nvarchar(256) collate database_default , GenProcID varchar3(38) collate database_default , ObjectKey varchar(138) collate database_default , DisplayName nvarchar(1024) collate database_default , ProcessState nchar4(1) collate database_default , XUserInserted nvarchar(64) collate database_default , XUserUpdated nvarchar(64) collate database_default , XDateInserted5 datetime , XDateUpdated datetime , Origin int ) with schemabinding as begin  declare @Work dbo.QBM_YCursorBuffer  declare @Children dbo.QBM_YParameterList6 if @ChildrenList > ' ' begin insert into @Children(Parameter1) select y.Value from string_split(@ChildrenList, char(7)) as y where y.value > ' '  if @@ROWCOUNT7 > 0  begin insert into @Work(UID1 , Int1 ) select p.GenProcID , 17 as Origin from dbo.DialogProcess p with (readpast) join @Children l on p.ObjectKey 8= l.Parameter1 union all select p.GenProcID , 18 as Origin from dbo.DialogProcessChain p with (readpast) join @Children l on p.ObjectKey = l.Parameter19 end end if @ObjectKey > ' ' begin insert into @Work(UID1 , Int1 ) select p.GenProcID , 1 as Origin from dbo.DialogProcess p with (readpast) where p.ObjectKey10 = @ObjectKey union all select p.GenProcID , 2 as Origin from dbo.DialogProcessChain p with (readpast) where p.ObjectKey = @ObjectKey end if @XUserInserted11 > ' ' begin insert into @Work(UID1 , Int1 ) select p.GenProcID , 1 as Origin from dbo.DialogProcess p with (readpast) where p.XUserInserted = @XUserInserted12 union all select p.GenProcID , 2 as Origin from dbo.DialogProcessChain p with (readpast) where p.XUserInserted = @XUserInserted end if @ObjectKey > ' '13 and @IncludeSubstitution = 1 begin  insert into @Work(UID1 , Int1 ) select s.GenProcIDNew as GenProcID , 4 as Origin from dbo.DialogProcessSubstitute 14s with (readpast) where exists (select top 1 1 from dbo.DialogProcess p with (readpast) where p.ObjectKey = @ObjectKey  and p.GenProcID = s.GenProcIDOrigin15 )  and s.ReadyForDeleteOrExport = 0 union all  select s.GenProcIDOrigin as GenProcID , 8 as Origin from dbo.DialogProcessSubstitute s with (readpast) 16where exists (select top 1 1 from dbo.DialogProcess p with (readpast) where p.ObjectKey = @ObjectKey  and p.genprocId = s.GenProcIDNew )  and s.ReadyForDeleteOrExport17 = 0 end if @XUserInserted > ' ' and @IncludeSubstitution = 1 begin  insert into @Work(UID1 , Int1 ) select s.GenProcIDNew as GenProcID , 4 as Origin from18 dbo.DialogProcessSubstitute s with (readpast) where exists (select top 1 1 from dbo.DialogProcess p with (readpast) where p.XUserInserted = @XUserInserted19  and p.GenProcID = s.GenProcIDOrigin )  and s.ReadyForDeleteOrExport = 0 union all  select s.GenProcIDOrigin as GenProcID , 8 as Origin from dbo.DialogProcessSubstitute20 s with (readpast) where exists (select top 1 1 from dbo.DialogProcess p with (readpast) where p.XUserInserted = @XUserInserted  and p.genprocId = s.GenProcIDNew21 )  and s.ReadyForDeleteOrExport = 0 end insert into @erg(BasisObjectType, CustomComment, GenProcID, ObjectKey, DisplayName , ProcessState, XUserInserted22, XDateInserted, XDateUpdated, Origin , XUserUpdated ) select d.BasisObjectType, d.CustomComment, d.GenProcID, d.ObjectKey, d.DisplayName ,d.ProcessState23, d.XUserInserted, d.XDateInserted, d.XDateUpdated, x.Origin , d.XUserUpdated from dbo.DialogProcess d with (readpast) join ( select w.UID1  , MAX(w.Int124  & 1) + MAX(w.Int1  & 2) +MAX(w.Int1  & 4) + MAX(w.Int1  & 8) + MAX(w.Int1  & 16) as Origin from @Work w group by w.UID1  ) as x on d.GenProcID = x.UID125  where exists  ( select top 1 1 from dbo.DialogProcessChain pc with (readpast) where pc.GenProcID = d.GenProcID )   return end 26