Back to OIM Explorer

dbo.QBM_PDialogUserInGroupCorrect

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.535 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_FCVElementToObjectKey2 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

Complete Source

SQL170 lines
1CREATE PROCEDURE QBM_PDialogUserInGroupCorrect(2  @UID_DialogUserPattern varchar(38) = '%'3)4AS5BEGIN6  DECLARE @XUser nvarchar(64) = object_name(@@procid)7  DECLARE @XDate datetime = getutcdate()8  DECLARE @defaultGroup varchar(38) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5'9  DECLARE @Debugswitch int = 010  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')11  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')12  DECLARE @cmd nvarchar(max)13  SET XACT_ABORT OFF14  BEGIN TRY15    IF @Debugswitch > 016    BEGIN17      print 'entry : ' + @XUser18    END19    EXEC QBM_PSessionContextSet 'XUSER',20      @XUser21    INSERT INTO DialogUserInGroup(UID_DialogGroup,22    UID_DialogUser,23    XDateInserted,24    XDateUpdated,25    XUserInserted,26    XUserUpdated,27    XObjectKey)28    SELECT29      DISTINCT G.UID_DialogGroup,30      U.UID_DialogUser,31      @XDate,32      @XDate,33      @XUser,34      @XUser,35      dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup',36      'UID_DialogGroup',37      G.UID_DialogGroup,38      'UID_DialogUser',39      U.UID_DialogUser)40    FROM DialogUser U41    JOIN DialogGroup G42      ON G.UID_DialogGroup = @defaultGroup43    WHERE44      NOT EXISTS(45    SELECT TOP 1 146    FROM DialogUserInGroup UiG47    WHERE48      UiG.UID_DialogUser = U.UID_DialogUser AND UiG.UID_DialogGroup = G.UID_DialogGroup)49    IF EXISTS(50      SELECT TOP 1 151      FROM QBM_VSchemaColumns c52      WHERE53        c.TABLE_NAME = 'DialogGroup' AND c.COLUMN_NAME = 'IsRoleBasedOnly')54    BEGIN55      IF @Debugswitch > 056      BEGIN57        print 'rolebased only exists : ' + @XUser58      END59      SELECT60        @cmd = '61	declare @XDate datetime = getutcdate()6263  insert into DialogUserInGroup ( UID_DialogGroup, UID_DialogUser, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey)64      select distinct G.UID_DialogGroup, U.UID_DialogUser, @XDate, @XDate, '''65        + @XUser + ''', ''' + @XUser + '''66				  , dbo.QBM_FCVElementToObjectKey2(''DialogUserInGroup'', ''UID_DialogGroup'', G.UID_DialogGroup, ''UID_DialogUser'', U.UID_DialogUser)67	  -- select g.GroupName, u.UserName, u.IsAdmin, u.IsDynamicUser 68  from DialogUser U join DialogGroup G on 1=169   where u.IsAdmin = 170	      and u.IsDynamicUser = 071		  and u.IsServiceAccount = 072	      and u.UID_DialogUser like '''73        + @UID_DialogUserPattern + '''74	      -- diese Zeile ist der einzige Unterschied75	      and g.IsRoleBasedOnly = 076	      and not exists (select top 1 177						      from DialogUserInGroup UiG78						      where UiG.UID_DialogUser = U.UID_DialogUser79							      and UiG.UID_DialogGroup = G.UID_DialogGroup80					      )81	'82      EXEC sp_executesql @cmd83    END84    ELSE85    BEGIN86      IF @Debugswitch > 087      BEGIN88        print 'rolebased only not exists : ' + @XUser89      END90      INSERT INTO DialogUserInGroup(UID_DialogGroup,91      UID_DialogUser,92      XDateInserted,93      XDateUpdated,94      XUserInserted,95      XUserUpdated,96      XObjectKey)97      SELECT98        DISTINCT G.UID_DialogGroup,99        U.UID_DialogUser,100        @XDate,101        @XDate,102        @XUser,103        @XUser,104        dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup',105        'UID_DialogGroup',106        G.UID_DialogGroup,107        'UID_DialogUser',108        U.UID_DialogUser)109      FROM DialogUser U110      JOIN DialogGroup G111        ON 1=1112      WHERE113        u.IsAdmin = 1 AND u.IsDynamicUser = 0 AND u.IsServiceAccount = 0 AND u.UID_DialogUser LIKE @UID_DialogUserPattern AND NOT EXISTS(114      SELECT TOP 1 1115      FROM DialogUserInGroup UiG116      WHERE117        UiG.UID_DialogUser = U.UID_DialogUser AND UiG.UID_DialogGroup = G.UID_DialogGroup)118    END119    IF EXISTS(120      SELECT TOP 1 1121      FROM QBM_VSchemaColumns c122      WHERE123        c.TABLE_NAME = 'DialogGroup' AND c.COLUMN_NAME = 'IsRoleBasedOnly')124    BEGIN125      SELECT126        @cmd = '127    delete DialogUserInGroup 128		    from DialogUserInGroup uig join DialogUser u on uig.UID_DialogUser = u.UID_DialogUser129								    join DialogGroup g on uig.UID_DialogGroup = g.UID_DialogGroup130		    where ((u.IsDynamicUser <> G.IsRoleBasedOnly 131					)132				  or u.IsServiceAccount = 1133				  )134				and u.UID_DialogUser like '''135        + rtrim(@UID_DialogUserPattern) + '''136		     and g.UID_DialogGroup <> ''' + @defaultGroup + '''		-- default rights137		  '138      EXEC sp_executesql @cmd139    END140    ELSE141    BEGIN142      DELETE DialogUserInGroup143      FROM DialogUserInGroup uig144      JOIN DialogUser u145        ON uig.UID_DialogUser = u.UID_DialogUser146      JOIN DialogGroup g147        ON uig.UID_DialogGroup = g.UID_DialogGroup148      WHERE149        (u.IsDynamicUser = 1 OR u.IsServiceAccount = 1) AND u.UID_DialogUser LIKE rtrim(@UID_DialogUserPattern) AND g.UID_DialogGroup <> @defaultGroup150    END151  END TRY152  BEGIN CATCH153    EXEC QBM_PSessionErrorAdd DEFAULT154    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()155    RAISERROR(@Rethrow,156    18,157    1)158      WITH NOWAIT159  END CATCH160  ende:161  IF @Debugswitch > 0162  BEGIN163    print 'exit : ' + @XUser164  END165  EXEC QBM_PSessionContextSet 'GenProcID',166    @GenProcID_R167  EXEC QBM_PSessionContextSet 'XUser',168    @XUser_R169  RETURN170END
Open raw exported source
SQL ยท Raw53 lines
1   create   procedure QBM_PDialogUserInGroupCorrect (@UID_DialogUserPattern varchar(38) = '%' ) as begin declare @XUser nvarchar(64) = object_name2(@@procid) declare @XDate datetime = getutcdate() declare @defaultGroup varchar(38) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5' declare @Debugswitch int =3 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @cmd nvarchar4(max) SET XACT_ABORT OFF BEGIN TRY if @Debugswitch > 0 begin print 'entry : ' + @XUser end exec QBM_PSessionContextSet 'XUSER', @XUser  insert into DialogUserInGroup5 ( UID_DialogGroup, UID_DialogUser, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey) select distinct G.UID_DialogGroup, U.UID_DialogUser6, @XDate, @XDate, @XUser, @XUser , dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup', 'UID_DialogGroup', G.UID_DialogGroup, 'UID_DialogUser', U.UID_DialogUser7)  from DialogUser U join DialogGroup G on G.UID_DialogGroup = @defaultGroup where not exists (select top 1 1 from DialogUserInGroup UiG where UiG.UID_DialogUser8 = U.UID_DialogUser and UiG.UID_DialogGroup = G.UID_DialogGroup ) if exists (select top 1 1 from QBM_VSchemaColumns c where c.TABLE_NAME = 'DialogGroup'9 and c.COLUMN_NAME = 'IsRoleBasedOnly' ) begin if @Debugswitch > 0 begin print 'rolebased only exists : ' + @XUser end select @cmd = '10	declare @XDate datetime = getutcdate()1112  insert into DialogUserInGroup ( UID_DialogGroup, UID_DialogUser, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey)13      select distinct G.UID_DialogGroup, U.UID_DialogUser, @XDate, @XDate, '''14 + @XUser + ''', ''' + @XUser + '''15				  , dbo.QBM_FCVElementToObjectKey2(''DialogUserInGroup'', ''UID_DialogGroup'', G.UID_DialogGroup, ''UID_DialogUser'', U.UID_DialogUser)16	  -- select g.GroupName, u.UserName, u.IsAdmin, u.IsDynamicUser 17  from DialogUser U join DialogGroup G on 1=118   where u.IsAdmin = 119	      and u.IsDynamicUser = 020		  and u.IsServiceAccount = 021	      and u.UID_DialogUser like '''22 + @UID_DialogUserPattern + '''23	      -- diese Zeile ist der einzige Unterschied24	      and g.IsRoleBasedOnly = 025	      and not exists (select top 1 126						      from DialogUserInGroup UiG27						      where UiG.UID_DialogUser = U.UID_DialogUser28							      and UiG.UID_DialogGroup = G.UID_DialogGroup29					      )30	'31 exec sp_executesql @cmd end else begin if @Debugswitch > 0 begin print 'rolebased only not exists : ' + @XUser end insert into DialogUserInGroup ( UID_DialogGroup32, UID_DialogUser, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey) select distinct G.UID_DialogGroup, U.UID_DialogUser, @XDate, @XDate33, @XUser, @XUser , dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup', 'UID_DialogGroup', G.UID_DialogGroup, 'UID_DialogUser', U.UID_DialogUser)  from 34DialogUser U join DialogGroup G on 1=1 where u.IsAdmin = 1 and u.IsDynamicUser = 0 and u.IsServiceAccount = 0 and u.UID_DialogUser like @UID_DialogUserPattern35 and not exists (select top 1 1 from DialogUserInGroup UiG where UiG.UID_DialogUser = U.UID_DialogUser and UiG.UID_DialogGroup = G.UID_DialogGroup ) end36   if exists (select top 1 1 from QBM_VSchemaColumns c where c.TABLE_NAME = 'DialogGroup' and c.COLUMN_NAME = 'IsRoleBasedOnly' ) begin select @cmd = '37    delete DialogUserInGroup 38		    from DialogUserInGroup uig join DialogUser u on uig.UID_DialogUser = u.UID_DialogUser39								    join DialogGroup g on uig.UID_DialogGroup = g.UID_DialogGroup40		    where ((u.IsDynamicUser <> G.IsRoleBasedOnly 41					)42				  or u.IsServiceAccount = 143				  )44				and u.UID_DialogUser like '''45 + rtrim(@UID_DialogUserPattern) + '''46		     and g.UID_DialogGroup <> ''' + @defaultGroup + '''		-- default rights47		  ' exec sp_executesql @cmd end48 else  begin delete DialogUserInGroup  from DialogUserInGroup uig join DialogUser u on uig.UID_DialogUser = u.UID_DialogUser join DialogGroup g on uig.UID_DialogGroup49 = g.UID_DialogGroup where ( u.IsDynamicUser = 1 or u.IsServiceAccount = 1 ) and u.UID_DialogUser like rtrim(@UID_DialogUserPattern) and g.UID_DialogGroup50 <> @defaultGroup  end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR51 (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: if @Debugswitch > 0 begin print 'exit : ' + @XUser end exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R52 exec QBM_PSessionContextSet 'XUser', @XUser_R return end 53