Back to OIM Explorer

dbo.QBM_PUserDetectByGroupList

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.553 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_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVElementToObjectKey2 source text reference
  • references source dbo.QBM_FCVGuidToTransfer source text reference
  • references source dbo.QBM_FGIModuleExists source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL205 lines
1CREATE PROCEDURE QBM_PUserDetectByGroupList(2  @GroupGuidList nvarchar(max),3  @uid_DialogUser varchar(38) OUTPUT,4  @UID_QBMProduct varchar(38) = NULL5)6AS7BEGIN8  DECLARE @GenProcID varchar(38)9  DECLARE @XUser nvarchar(64)10  DECLARE @XDate datetime = getutcdate()11  DECLARE @cmd nvarchar(max)12  DECLARE @DebugSwitch int = 013  DECLARE @groups QBM_YSingleGUID14  DECLARE @anzahlGruppen int15  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')16  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')17  DECLARE @QBM_BaseRight varchar(38) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5'18  SET XACT_ABORT OFF19  BEGIN TRY20    SELECT @GenProcID = @GenProcID_R21    IF isnull(@XUser,22    N '') = N ''23    BEGIN24      SELECT @XUser = N 'DBScheduler'25    END26    SELECT27      @cmd = CONCAT('	select g.uid_DialogGroup 28						from DialogGroup g join dbo.QBM_FCVStringToList(''',29      @GroupGuidList,30      ''', N''|'',1 ,0 ) l on l.ParameterValue = g.UID_DialogGroup31						',32    CASE33      WHEN dbo.QBM_FGIModuleExists('QER') = 1 THEN34    '35						where g.IsRoleBasedOnly = 1'36    ELSE ''37    END,38    '39						union 40						select ''',41    @QBM_BaseRight,42    '''43')44    IF @DebugSwitch > 045    BEGIN46      print @cmd47    END48    INSERT INTO @groups(UID_SingleGuid)49    EXEC sp_executesql @cmd50    DELETE @groups51    FROM @groups g52    WHERE53      g.UID_SingleGuid IN(54    SELECT li.UID_DialogGroup55    FROM DialogGroupInProductLimited li) AND NOT EXISTS(56    SELECT TOP 1 157    FROM DialogGroupInProductLimited li58    WHERE59      li.UID_DialogGroup = g.UID_SingleGuid AND li.UID_DialogProduct = @UID_QBMProduct)60    DELETE @groups61    FROM @Groups g62    WHERE63      EXISTS(64    SELECT TOP 1 165    FROM dialogGroupCollection co66    WHERE67      g.UID_SingleGuid = co.uid_dialogGroupParent AND co.uid_dialogGroup <> co.uid_dialogGroupParent AND co.uid_dialogGroup IN(68    SELECT e.UID_SingleGuid69    FROM @groups e)) AND g.UID_SingleGuid <> @QBM_BaseRight70    DELETE @groups71    FROM @Groups g72    WHERE73      NOT EXISTS(74    SELECT TOP 1 175    FROM dialogGroupCollection co76    WHERE77      g.UID_SingleGuid = co.uid_dialogGroup) AND g.UID_SingleGuid <> @QBM_BaseRight78    SELECT @AnzahlGruppen = count(*)79    FROM @groups80    IF @AnzahlGruppen = 081    BEGIN82      RAISERROR('#LDS#No valid group passed.|',83      18,84      2)85        WITH nowait86      RETURN87    END88    SELECT @uid_DialogUser = NULL89    SELECT TOP 1 @uid_DialogUser = y.uid_DialogUser90    FROM(91    SELECT x.uid_DialogUser92    FROM(93    SELECT uig.uid_DialogUser94    FROM @Groups g95    JOIN dialoguseringroup uig96      ON uig.uid_DialogGroup = g.UID_SingleGuid97    GROUP BY uig.uid_DialogUser98    HAVING count(*) = @AnzahlGruppen99    UNION all100    SELECT uig.uid_DialogUser101    FROM dialoguseringroup uig102    GROUP BY uig.uid_DialogUser103    HAVING count(*) = @AnzahlGruppen) AS x104    GROUP BY x.uid_DialogUser105    HAVING count(*) = 2) AS y106    JOIN dialoguser u107      ON y.uid_DialogUser = u.uid_DialogUser108    WHERE109      u.isDynamicUser = 1110    IF @uid_DialogUser IS NOT NULL111    BEGIN112      GOTO fertig113    END114    SELECT @uid_DialogUser = newid()115    IF EXISTS(116      SELECT TOP 1 1117      FROM DialogTable t118      WHERE119        t.TableName = 'DialogUser' AND t.IsModuleGUIDDefault = 1)120    BEGIN121      SELECT122        @uid_DialogUser = dbo.QBM_FCVGuidToTransfer(@UID_DialogUser,123        'CCC')124    END125    EXEC QBM_PSessionContextSet 'GenProcID',126      @GenProcID127    EXEC QBM_PSessionContextSet 'XUser',128      @XUser129    INSERT INTO dialoguser(uid_DialogUser,130    username,131    XObjectKey,132    password,133    XDateInserted,134    XDateUpdated,135    XUserInserted,136    XUserUpdated,137    CustomRemarks,138    isReadOnly,139    IsDynamicUser)140    SELECT141      @uid_DialogUser,142      @uid_DialogUser,143      dbo.QBM_FCVElementToObjectKey1('DialogUser',144      'uid_DialogUser',145      @uid_DialogUser),146      'UnableForDialogLogin ',147      @XDate,148      @XDate,149      N 'System',150      N 'QBM_PUserDetectByGroupList',151      N 'Created by System',152      0,153      1154    EXEC QBM_PSessionContextSet 'GenProcID',155      @GenProcID156    EXEC QBM_PSessionContextSet 'XUser',157      @XUser158    INSERT INTO dialoguseringroup(uid_DialogUser,159    uid_DialogGroup,160    XDateInserted,161    XDateUpdated,162    XUserInserted,163    XUserUpdated,164    XObjectKey)165    SELECT166      DISTINCT @uid_DialogUser,167      g.UID_SingleGuid,168      @XDate,169      @XDate,170      N 'System',171      N 'QBM_PUserDetectByGroupList',172      dbo.QBM_FCVElementToObjectKey2('dialoguseringroup',173      'uid_DialogUser',174      @uid_DialogUser,175      'uid_DialogGroup',176      g.UID_SingleGuid)177    FROM @groups g178    WHERE179      NOT EXISTS(180    SELECT TOP 1 1181    FROM DialogUserInGroup e182    WHERE183      e.UID_DialogUser = @uid_DialogUser AND e.UID_DialogGroup = g.UID_SingleGuid) fertig:184    UPDATE dialoguser185    SET lastlogin = @XDate,186    XDateUpdated = @Xdate,187    XUserUpdated = @XUser188    WHERE189      uid_DialogUser = @uid_DialogUser190  END TRY191  BEGIN CATCH192    EXEC QBM_PSessionErrorAdd DEFAULT193    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()194    RAISERROR(@Rethrow,195    18,196    1)197      WITH NOWAIT198  END CATCH199  ende:200  EXEC QBM_PSessionContextSet 'GenProcID',201    @GenProcID_R202  EXEC QBM_PSessionContextSet 'XUser',203    @XUser_R204  RETURN205END
Open raw exported source
SQL ยท Raw36 lines
1    create   procedure QBM_PUserDetectByGroupList (@GroupGuidList nvarchar(max),  @uid_DialogUser varchar(38) output , @UID_QBMProduct varchar(382) = null ) as begin  declare @GenProcID varchar(38) declare @XUser nvarchar(64) declare @XDate datetime = getutcdate() declare @cmd nvarchar(max) declare3 @DebugSwitch int = 0 declare @groups QBM_YSingleGUID  declare @anzahlGruppen int declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare4 @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @QBM_BaseRight varchar(38) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5' SET XACT_ABORT 5OFF BEGIN TRY select @GenProcID = @GenProcID_R if isnull(@XUser, N'') = N'' begin select @XUser = N'DBScheduler' end select @cmd = concat( '	select g.uid_DialogGroup 6						from DialogGroup g join dbo.QBM_FCVStringToList('''7, @GroupGuidList , ''', N''|'',1 ,0 ) l on l.ParameterValue = g.UID_DialogGroup8						', case when dbo.QBM_FGIModuleExists('QER') = 1 then '9						where g.IsRoleBasedOnly = 1'10 else '' end, '11						union 12						select ''' , @QBM_BaseRight, '''13' ) if @DebugSwitch > 0 begin print @cmd end insert into @groups (UID_SingleGuid14 ) exec sp_executesql @cmd delete  @groups from @groups g where g.UID_SingleGuid in (select li.UID_DialogGroup from DialogGroupInProductLimited li ) and15 not exists (select top 1 1 from DialogGroupInProductLimited li where li.UID_DialogGroup = g.UID_SingleGuid and li.UID_DialogProduct = @UID_QBMProduct 16)   delete  @groups  from @Groups g where exists (select top 1 1 from dialogGroupCollection co where g.UID_SingleGuid  = co.uid_dialogGroupParent and co.uid_dialogGroup17 <> co.uid_dialogGroupParent and co.uid_dialogGroup in (select e.UID_SingleGuid from @groups e ) ) and g.UID_SingleGuid  <> @QBM_BaseRight delete  @groups18  from @Groups g where Not exists (select top 1 1 from dialogGroupCollection co where g.UID_SingleGuid  = co.uid_dialogGroup ) and g.UID_SingleGuid  <>19 @QBM_BaseRight  select @AnzahlGruppen = count(*) from @groups if @AnzahlGruppen = 0 begin  raiserror('#LDS#No valid group passed.|' , 18, 2) with nowait20 return end select @uid_DialogUser = null select top 1 @uid_DialogUser = y.uid_DialogUser from ( select x.uid_DialogUser from (  select uig.uid_DialogUser21 from @Groups g join dialoguseringroup uig on uig.uid_DialogGroup = g.UID_SingleGuid  group by uig.uid_DialogUser having count(*) = @AnzahlGruppen union22 all  select uig.uid_DialogUser from dialoguseringroup uig group by uig.uid_DialogUser having count(*) = @AnzahlGruppen ) as x group by x.uid_DialogUser23 having count(*) = 2 ) as y join dialoguser u on y.uid_DialogUser = u.uid_DialogUser where u.isDynamicUser = 1  if @uid_DialogUser is not null begin goto24 fertig end  select @uid_DialogUser = newid()   if exists (select top 1 1 from DialogTable t where t.TableName = 'DialogUser' and t.IsModuleGUIDDefault25 = 1 ) begin select @uid_DialogUser = dbo.QBM_FCVGuidToTransfer(@UID_DialogUser, 'CCC') end  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet26 'XUser', @XUser insert into dialoguser (uid_DialogUser, username, XObjectKey, password, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, CustomRemarks27 , isReadOnly, IsDynamicUser ) select @uid_DialogUser, @uid_DialogUser, dbo.QBM_FCVElementToObjectKey1('DialogUser', 'uid_DialogUser', @uid_DialogUser)28, 'UnableForDialogLogin ', @XDate, @XDate, N'System', N'QBM_PUserDetectByGroupList', N'Created by System' , 0, 1 exec QBM_PSessionContextSet 'GenProcID'29, @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser insert into dialoguseringroup (uid_DialogUser , uid_DialogGroup , XDateInserted, XDateUpdated,30 XUserInserted, XUserUpdated , XObjectKey) select distinct @uid_DialogUser, g.UID_SingleGuid , @XDate, @XDate, N'System', N'QBM_PUserDetectByGroupList'31, dbo.QBM_FCVElementToObjectKey2('dialoguseringroup', 'uid_DialogUser', @uid_DialogUser, 'uid_DialogGroup', g.UID_SingleGuid ) from @groups g  where not32 exists (select top 1 1 from DialogUserInGroup e where e.UID_DialogUser = @uid_DialogUser and e.UID_DialogGroup = g.UID_SingleGuid  ) fertig: update dialoguser33 set lastlogin = @XDate  , XDateUpdated = @Xdate , XUserUpdated = @XUser where uid_DialogUser = @uid_DialogUser END TRY BEGIN CATCH exec QBM_PSessionErrorAdd34 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: exec QBM_PSessionContextSet35 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 36