Back to OIM Explorer

dbo.QER_PIsForITShopFlagCheck

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 7.799 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_FCVStringToList source text reference
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL313 lines
1CREATE PROCEDURE QER_PIsForITShopFlagCheck(2  @TableName varchar(30),3  @ElementsToCheck QBM_YParameterList READONLY,4  @ChildrenToCheck varchar(max) = ''5)6AS7BEGIN8  DECLARE @BasetreeMN varchar(30)9  DECLARE @BasetreeMNPK varchar(30)10  DECLARE @TableMN varchar(30)11  DECLARE @TableMNPK varchar(30)12  DECLARE @ElementBuffer QBM_YCursorBuffer13  DECLARE @ElementCount int14  DECLARE @ElementIndex int15  DECLARE @DebugSwitch int = 016  DECLARE @SQLCmd nvarchar(max)17  DECLARE @Message nvarchar(max)18  DECLARE @PatternBaseTreeAus nvarchar(max) = '19 declare @erg int20  select top 1 @erg = 1 21				from #ITShopFlags i join @BasetreeMN bha on i.UID_PK = bha.@BasetreeMNPK22															and bha.XOrigin > 023															--and ohg.XIsInEffect = 124								join BaseTree b on bha.uid_org = b.uid_Org25				 where i.IsForITShopNew = 026					and i.IsITShopOnlyNew = 027					and (b.XObjectKey like ''<Key><T>ITShop___</T>%''28							or b.XObjectKey like ''<Key><T>MirrorShop</T>%''29						)30'31  DECLARE @PatternBasetreeAn nvarchar(max) = '32 declare @erg int33  select top 1 @erg = 1 34					from #ITShopFlags i join @BasetreeMN bha on i.UID_PK = bha.@BasetreeMNPK35																and bha.XOrigin > 036																--and ohg.XIsInEffect = 137									join BaseTree b on bha.uid_Org = b.UId_Org38					where i.IsForITShopNew = 139					and i.IsITShopOnlyNew = 140					and not (b.XObjectKey like ''<Key><T>ITShop___</T>%''41							 or b.XObjectKey like ''<Key><T>MirrorShop</T>%''42							)43'44  DECLARE @PatternAddOnChildren nvarchar(max) = '45 declare @erg int46  select top 1 @erg = 1 47					from #ITShopFlags i join @TableMN zuw on i.UID_PK = zuw.@TableMNPK48													and zuw.XOrigin & 0xFFFFFFFD /*dbo.QBM_F G I BitPatternXOrigin(''|Inherit|'', 1)*/ > 0 49													-- ohne XIsInEffect-Test, könnte ja jederzeit wieder angehen50					 where IsForITShopNew = 151					  and i.IsITShopOnlyNew = 152'53  SET XACT_ABORT OFF54  BEGIN TRY55    drop TABLE56    IF EXISTS #ITShopFlags57    CREATE TABLE #ITShopFlags(58      IsForITShopNew BIT,59      IsITShopOnlyNew BIT,60      UID_PK varchar(38) collate database_default,61      XObjectKey varchar(138) collate database_default62    )63    INSERT INTO #ITShopFlags(UID_PK,64    XObjectKey,65    IsForITShopNew,66    IsITShopOnlyNew67  )68  SELECT69    e.Parameter1,70    e.Parameter2,71    e.HasContentFull,72    dbo.QBM_FCVStringToBit(e.Parameter373    )74FROM @ElementsToCheck e75IF @debugswitch > 076BEGIN77  SELECT *78  FROM #ITShopFlags79END80SELECT81  @BasetreeMN = tba.TableName,82  @BasetreeMNPK = r.ChildColumn83FROM BaseTreeAssign a84JOIN DialogTable tba85  WITH(readpast86)87  ON a.UID_DialogTableMN = tba.UID_DialogTable88JOIN DialogTable tb89  WITH(readpast90)91  ON a.UID_DialogTableElement = tb.UID_DialogTable92JOIN QBM_VQBMRelation r93  ON r.ChildTable = tba.TableName AND r.ParentTable = @Tablename94WHERE95  tb.TableName = @Tablename96IF @DebugSwitch > 097BEGIN98  print @BasetreeMN print @BasetreeMNPK99END100INSERT INTO @ElementBuffer(Ident1,101Ident2102)103SELECT104  rp.ChildTable,105  rp.ChildColumn106FROM BaseTreeAssign a107JOIN DialogTable tba108  WITH(readpast109)110  ON a.UID_DialogTableMN = tba.UID_DialogTable111JOIN DialogTable tb112  WITH(readpast113)114  ON a.UID_DialogTableElement = tb.UID_DialogTable115JOIN QBM_VQBMRelation r116  ON r.ChildTable = tba.TableName AND r.ParentTable = @Tablename117JOIN QBM_VQBMRelation rp118  ON rp.ParentTable = @Tablename AND a.UID_TaskPerson > ' ' AND rp.IsMNRelation = 1119JOIN QBM_VQBMRelation rph120  ON rp.UID_QBMRelationMN = rph.UID_QBMRelation AND rph.ParentTable = 'Person'121WHERE122  tb.TableName = @Tablename123UNION124SELECT125  rp.ChildTable,126  rp.ChildColumn127FROM BaseTreeAssign a128JOIN DialogTable tba129  WITH(readpast130)131  ON a.UID_DialogTableMN = tba.UID_DialogTable132JOIN DialogTable tb133  WITH(readpast134)135  ON a.UID_DialogTableElement = tb.UID_DialogTable136JOIN QBM_VQBMRelation r137  ON r.ChildTable = tba.TableName AND r.ParentTable = @Tablename138JOIN QBM_VQBMRelation rp139  ON rp.ParentTable = @Tablename AND a.UID_TaskWorkDesk > ' ' AND rp.IsMNRelation = 1140JOIN QBM_VQBMRelation rph141  ON rp.UID_QBMRelationMN = rph.UID_QBMRelation AND rph.ParentTable = 'WorkDesk'142WHERE143  tb.TableName = @Tablename144UNION145SELECT146  rp.ChildTable,147  rp.ChildColumn148FROM BaseTreeAssign a149JOIN DialogTable tba150  WITH(readpast151)152  ON a.UID_DialogTableMN = tba.UID_DialogTable153JOIN DialogTable tb154  WITH(readpast155)156  ON a.UID_DialogTableElement = tb.UID_DialogTable157JOIN QBM_VQBMRelation r158  ON r.ChildTable = tba.TableName AND r.ParentTable = @Tablename159JOIN QBM_VQBMRelation rp160  ON rp.ParentTable = @Tablename AND a.UID_TaskHardware > ' ' AND rp.IsMNRelation = 1161JOIN QBM_VQBMRelation rph162  ON rp.UID_QBMRelationMN = rph.UID_QBMRelation AND rph.ParentTable = 'Hardware'163WHERE164  tb.TableName = @Tablename165UNION166SELECT167  r.ChildTable,168  r.ChildColumn169FROM QBM_VQBMRelation r170JOIN dbo.QBM_FCVStringToList(@ChildrenToCheck,171'|',1721,1730174)175AS176c177  ON r.ChildTable = c.ParameterValue178WHERE179  r.ParentTable = @Tablename AND r.IsMNRelation = 1180SELECT @ElementCount = @@ROWCOUNT181IF @DebugSwitch > 0182BEGIN183  SELECT184    e.Ident1,185    e.Ident2186  FROM @ElementBuffer e187END188IF EXISTS(189  SELECT TOP 1 1190  FROM #ITShopFlags i191  WHERE192    i.IsForITShopNew = 0 AND i.IsITShopOnlyNew = 1)193BEGIN194  RAISERROR('#LDS#Invalid flag combination for IsForITShop and IsITShopOnly.|',195  18,196  2)197    WITH nowait198END199SELECT200  @SQLCmd = replace(replace(@PatternBaseTreeAus, '@BasetreeMNPK', @BasetreeMNPK),201  '@BasetreeMN',202  @BasetreeMN)203IF @DebugSwitch > 0204BEGIN205  print @SQLCmd206END207EXEC sp_executesql @SQLCMd208IF @@ROWCOUNT > 0209BEGIN210  RAISERROR('#LDS#Changes cannot take place, because assignments still exist within IT Shop structures.|',211  18,212  2)213    WITH nowait214END215IF NOT EXISTS(216  SELECT TOP 1 1217  FROM #ITShopFlags i218  WHERE219    i.IsForITShopNew = 1 AND i.IsITShopOnlyNew = 1)220BEGIN221  GOTO endLabel222END223SELECT224  @SQLCmd = replace(replace(@PatternBasetreeAn, '@BasetreeMNPK', @BasetreeMNPK),225  '@BasetreeMN',226  @BasetreeMN)227IF @DebugSwitch > 0228BEGIN229  print @SQLCmd230END231EXEC sp_executesql @SQLCMd232IF @@ROWCOUNT > 0233BEGIN234  RAISERROR('#LDS#Changes cannot take place, because assignments still exist outside IT Shop structures.|',235  18,236  2)237    WITH nowait238END239SELECT @ElementIndex = 1240WHILE @ElementIndex <= @ElementCount241BEGIN242  SELECT243    TOP 1 @TableMN = bu.Ident1,244    @TableMNPK = bu.Ident2245  FROM @ElementBuffer bu246  WHERE247    bu.ElementIndex = @ElementIndex248  SELECT249    @SQLCmd = replace(replace(@PatternAddOnChildren, '@TableMNPK', @TableMNPK),250    '@TableMN',251    @TableMN)252  IF @DebugSwitch > 0253  BEGIN254    print @SQLCmd255  END256  EXEC sp_executesql @SQLCMd257  IF @@ROWCOUNT > 0258  BEGIN259    SELECT260      @Message = '#LDS#Changes cannot take place because direct assignments still exist. Table: {0}.|' + @TableMNPK + '|'261    RAISERROR(@message,262    18,263    2)264      WITH nowait265  END266  SELECT @ElementIndex += 1267END268IF EXISTS(269  SELECT TOP 1 1270  FROM #ITShopFlags i271  JOIN ESetHasEntitlement rhr272    ON rhr.Entitlement = i.XObjectKey AND rhr.XOrigin > 0273  JOIN BaseTreeHasESet ohs274    ON ohs.XOrigin > 0 AND ohs.XIsInEffect = 1 AND ohs.uid_ESet = rhr.uid_ESet275  JOIN BaseTree o276    ON o.uid_org = ohs.uid_org277  WHERE278    o.UID_OrgRoot NOT IN('QER-V-ITShopOrg', 'QER-V-ITShopSrc') AND i.IsITShopOnlyNew = 1)279BEGIN280  RAISERROR('#LDS#Change cannot take place because the resource belongs to a package which is assigned to a base tree outside the IT Shop.|',281  18,282  2)283    WITH nowait284END285IF EXISTS(286  SELECT TOP 1 1287  FROM #ITShopFlags i288  JOIN ESetHasEntitlement ehe289    ON i.XObjectKey = ehe.Entitlement AND ehe.XOrigin > 0290  JOIN ESet e291    ON ehe.uid_ESet = e.uid_Eset AND e.IsITShopOnly = 0292  WHERE293    i.IsForITShopNew = 1 AND i.IsITShopOnlyNew = 1)294BEGIN295  RAISERROR('#LDS#Changes cannot take place, because assignments to system roles still exist that may not be used exclusively in IT Shop.|',296  18,297  2)298    WITH nowait299END300END TRY301BEGIN CATCH302  EXEC QBM_PSessionErrorAdd DEFAULT303  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()304  SET XACT_ABORT305    ON306  RAISERROR(@Rethrow,307  18,308  1)309    WITH NOWAIT310END CATCH311endLabel: truncate TABLE #ITShopFlags312RETURN313END
Open raw exported source
SQL · Raw75 lines
1           create   procedure QER_PIsForITShopFlagCheck (@TableName varchar(30) , @ElementsToCheck QBM_YParameterList readOnly   , @ChildrenToCheck2 varchar(max) = ''  ) as begin  declare @BasetreeMN varchar(30) declare @BasetreeMNPK varchar(30) declare @TableMN varchar(30) declare @TableMNPK varchar3(30) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int = 0 declare @SQLCmd nvarchar4(max) declare @Message nvarchar(max) declare @PatternBaseTreeAus nvarchar(max) = '5 declare @erg int6  select top 1 @erg = 1 7				from #ITShopFlags i join @BasetreeMN bha on i.UID_PK = bha.@BasetreeMNPK8															and bha.XOrigin > 09															--and ohg.XIsInEffect = 110								join BaseTree b on bha.uid_org = b.uid_Org11				 where i.IsForITShopNew = 012					and i.IsITShopOnlyNew = 013					and (b.XObjectKey like ''<Key><T>ITShop___</T>%''14							or b.XObjectKey like ''<Key><T>MirrorShop</T>%''15						)16'17 declare @PatternBasetreeAn nvarchar(max) = '18 declare @erg int19  select top 1 @erg = 1 20					from #ITShopFlags i join @BasetreeMN bha on i.UID_PK = bha.@BasetreeMNPK21																and bha.XOrigin > 022																--and ohg.XIsInEffect = 123									join BaseTree b on bha.uid_Org = b.UId_Org24					where i.IsForITShopNew = 125					and i.IsITShopOnlyNew = 126					and not (b.XObjectKey like ''<Key><T>ITShop___</T>%''27							 or b.XObjectKey like ''<Key><T>MirrorShop</T>%''28							)29'30 declare @PatternAddOnChildren nvarchar(max) = '31 declare @erg int32  select top 1 @erg = 1 33					from #ITShopFlags i join @TableMN zuw on i.UID_PK = zuw.@TableMNPK34													and zuw.XOrigin & 0xFFFFFFFD /*dbo.QBM_F G I BitPatternXOrigin(''|Inherit|'', 1)*/ > 0 35													-- ohne XIsInEffect-Test, könnte ja jederzeit wieder angehen36					 where IsForITShopNew = 137					  and i.IsITShopOnlyNew = 138'39 SET XACT_ABORT OFF BEGIN TRY drop table if exists #ITShopFlags create table #ITShopFlags (IsForITShopNew bit , IsITShopOnlyNew bit , UID_PK varchar(3840) collate database_default , XObjectKey varchar(138) collate database_default ) insert into #ITShopFlags(UID_PK, XObjectKey, IsForITShopNew, IsITShopOnlyNew41) select e.Parameter1, e.Parameter2, e.HasContentFull, dbo.QBM_FCVStringToBit(e.Parameter3) from @ElementsToCheck e if @debugswitch > 0 begin select * 42from #ITShopFlags end  select @BasetreeMN = tba.TableName , @BasetreeMNPK = r.ChildColumn from BaseTreeAssign a join DialogTable tba with (readpast) on43 a.UID_DialogTableMN = tba.UID_DialogTable join DialogTable tb with (readpast) on a.UID_DialogTableElement = tb.UID_DialogTable join QBM_VQBMRelation r44 on r.ChildTable = tba.TableName and r.ParentTable = @Tablename where tb.TableName = @Tablename if @DebugSwitch > 0 begin print @BasetreeMN print @BasetreeMNPK45 end  insert into @ElementBuffer(Ident1, Ident2) select rp.ChildTable, rp.ChildColumn from BaseTreeAssign a join DialogTable tba with (readpast) on a.UID_DialogTableMN46 = tba.UID_DialogTable join DialogTable tb with (readpast) on a.UID_DialogTableElement = tb.UID_DialogTable join QBM_VQBMRelation r on r.ChildTable = tba.TableName47 and r.ParentTable = @Tablename join QBM_VQBMRelation rp on rp.ParentTable = @Tablename and a.UID_TaskPerson > ' ' and rp.IsMNRelation = 1 join QBM_VQBMRelation48 rph on rp.UID_QBMRelationMN = rph.UID_QBMRelation and rph.ParentTable = 'Person' where tb.TableName = @Tablename union select rp.ChildTable , rp.ChildColumn49 from BaseTreeAssign a join DialogTable tba with (readpast) on a.UID_DialogTableMN = tba.UID_DialogTable join DialogTable tb with (readpast) on a.UID_DialogTableElement50 = tb.UID_DialogTable join QBM_VQBMRelation r on r.ChildTable = tba.TableName and r.ParentTable = @Tablename join QBM_VQBMRelation rp on rp.ParentTable51 = @Tablename and a.UID_TaskWorkDesk > ' ' and rp.IsMNRelation = 1 join QBM_VQBMRelation rph on rp.UID_QBMRelationMN = rph.UID_QBMRelation and rph.ParentTable52 = 'WorkDesk' where tb.TableName = @Tablename union select rp.ChildTable, rp.ChildColumn from BaseTreeAssign a join DialogTable tba with (readpast) on 53a.UID_DialogTableMN = tba.UID_DialogTable join DialogTable tb with (readpast) on a.UID_DialogTableElement = tb.UID_DialogTable join QBM_VQBMRelation r 54on r.ChildTable = tba.TableName and r.ParentTable = @Tablename join QBM_VQBMRelation rp on rp.ParentTable = @Tablename and a.UID_TaskHardware > ' ' and55 rp.IsMNRelation = 1 join QBM_VQBMRelation rph on rp.UID_QBMRelationMN = rph.UID_QBMRelation and rph.ParentTable = 'Hardware' where tb.TableName = @Tablename56 union select r.ChildTable, r.ChildColumn from QBM_VQBMRelation r join dbo.QBM_FCVStringToList(@ChildrenToCheck, '|', 1,0) as c on r.ChildTable = c.ParameterValue57 where r.ParentTable = @Tablename and r.IsMNRelation = 1 select @ElementCount = @@ROWCOUNT if @DebugSwitch > 0 begin select e.Ident1, e.Ident2 from @ElementBuffer58 e end  if exists( select top 1 1 from #ITShopFlags i where i.IsForITShopNew = 0 and i.IsITShopOnlyNew = 1 ) begin raiserror( '#LDS#Invalid flag combination for IsForITShop and IsITShopOnly.|'59, 18, 2) with nowait end     select @SQLCmd = replace(replace(@PatternBaseTreeAus  , '@BasetreeMNPK', @BasetreeMNPK) , '@BasetreeMN', @BasetreeMN) if @DebugSwitch60 > 0 begin print @SQLCmd end          exec sp_executesql @SQLCMd if @@ROWCOUNT > 0 begin raiserror( '#LDS#Changes cannot take place, because assignments still exist within IT Shop structures.|'61, 18, 2) with nowait end  if not exists(select top 1 1 from #ITShopFlags i where i.IsForITShopNew = 1 and i.IsITShopOnlyNew = 1 ) begin   goto endLabel62 end    select @SQLCmd = replace(replace(@PatternBasetreeAn , '@BasetreeMNPK', @BasetreeMNPK) , '@BasetreeMN', @BasetreeMN) if @DebugSwitch > 0 begin print63 @SQLCmd end          exec sp_executesql @SQLCMd if @@ROWCOUNT > 0 begin raiserror( '#LDS#Changes cannot take place, because assignments still exist outside IT Shop structures.|'64, 18, 2) with nowait end           select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableMN = bu.Ident1 , @TableMNPK =65 bu.Ident2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @SQLCmd = replace(replace(@PatternAddOnChildren , '@TableMNPK', @TableMNPK66) , '@TableMN', @TableMN) if @DebugSwitch > 0 begin print @SQLCmd end exec sp_executesql @SQLCMd if @@ROWCOUNT > 0 begin select @Message = '#LDS#Changes cannot take place because direct assignments still exist. Table: {0}.|'67 + @TableMNPK + '|' raiserror( @message, 18, 2) with nowait end select @ElementIndex += 1 end     if exists (select top 1 1 from #ITShopFlags i join ESetHasEntitlement68 rhr on rhr.Entitlement = i.XObjectKey and rhr.XOrigin > 0  join BaseTreeHasESet ohs on ohs.XOrigin > 0 and ohs.XIsInEffect = 1 and ohs.uid_ESet = rhr.uid_ESet69 join BaseTree o on o.uid_org = ohs.uid_org where o.UID_OrgRoot not in ('QER-V-ITShopOrg', 'QER-V-ITShopSrc') and i.IsITShopOnlyNew = 1 ) begin raiserror70( '#LDS#Change cannot take place because the resource belongs to a package which is assigned to a base tree outside the IT Shop.|', 18, 2) with nowait 71end    if exists (select top 1 1 from #ITShopFlags i join ESetHasEntitlement ehe on i.XObjectKey = ehe.Entitlement and ehe.XOrigin > 0  join ESet e on 72ehe.uid_ESet = e.uid_Eset and e.IsITShopOnly = 0 where i.IsForITShopNew = 1 and i.IsITShopOnlyNew = 1 ) begin raiserror( '#LDS#Changes cannot take place, because assignments to system roles still exist that may not be used exclusively in IT Shop.|'73, 18, 2) with nowait end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() set XACT_ABORT74 ON  RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: truncate table #ITShopFlags return end 75