Back to OIM Explorer

dbo.QBM_PDynamicUserForPerson

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 7.100 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_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

Complete Source

SQL318 lines
1CREATE PROCEDURE QBM_PDynamicUserForPerson(2  @UID_Person varchar(38),3  @UID_QBMProduct varchar(38),4  @UID_DialogUser varchar(38) OUTPUT5)6AS7BEGIN8  DECLARE @GenProcID varchar(38)9  DECLARE @XUser nvarchar(64) = object_name(@@procid)10  DECLARE @XDate datetime = getutcdate()11  DECLARE @SQLCmd nvarchar(max)12  DECLARE @DebugSwitch int = 013  DECLARE @Startdate datetime = getutcdate()14  DECLARE @UID_QBMProduct_Intern varchar(38) = isnull(@UID_QBMProduct,15  '##') drop TABLE16  IF EXISTS #GroupsAvailable17  CREATE TABLE #GroupsAvailable(18    UID_DialogGroup varchar(38) collate database_default primary key(UID_DialogGroup)19  ) drop TABLE20  IF EXISTS #GroupsForPerson21  CREATE TABLE #GroupsForPerson(22    UID_SingleGUID varchar(38) collate database_default primary key(UID_SingleGUID)23  )24  DECLARE @anzahlGruppen int25  DECLARE @GenProcID_R varchar(3826) = dbo.QBM_FGISessionContext(''27)28DECLARE @XUser_R nvarchar(6429) = dbo.QBM_FGISessionContext('XUser'30)31DECLARE @QBM_BaseRight varchar(3832) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5'33SET XACT_ABORT OFF34BEGIN TRY35  SET nocount36    ON37  SELECT @GenProcID = @GenProcID_R38  SELECT39    @SQLCmd = CONCAT('select g.UID_DialogGroup-- , gl.*40			from DialogGroup g left outer join DialogGroupInProductLimited gl on g.UID_DialogGroup = gl.UID_DialogGroup41			where gl.UID_DialogGroup is null42			-- reduktion um die Gruppen, die für Rolebased zur Verfügung stehen'43    ,44  CASE45    WHEN dbo.QBM_FGIModuleExists('QER') = 1 THEN46  '47						and g.IsRoleBasedOnly = 1'48  ELSE ''49  END,50  '51		union5253		-- die Gruppen für dieses Produkt54		select gl.UID_DialogGroup55			from DialogGroupInProductLimited gl join DialogGroup g on gl.UID_DialogGroup = g.UID_DialogGroup56			where gl.UID_DialogProduct = '''57  ,58  @UID_QBMProduct_Intern,59  '''60			-- reduktion um die Gruppen, die für Rolebased zur Verfügung stehen',61  CASE62    WHEN dbo.QBM_FGIModuleExists('QER') = 1 THEN63  '64						and g.IsRoleBasedOnly = 1'65  ELSE ''66  END,67  '68		union6970		select ''',71  @QBM_BaseRight,72  '''73		'74)75IF @DebugSwitch > 076BEGIN77  print @sqlcmd78END79INSERT INTO #GroupsAvailable(UID_DialogGroup80)81EXEC sp_executesql @SQLCmd82IF @DebugSwitch > 083BEGIN84  SELECT *85  FROM #GroupsAvailable86END87INSERT INTO #GroupsForPerson(UID_SingleGuid88)89SELECT90  DISTINCT UID_DialogGroup91FROM(92SELECT rp.UID_Org93AS94UID_AERole,95c.UID_Org,96rp.UID_DialogGroup,97rp.treelevel98FROM AERole r99JOIN BaseTreeCollectionF c100  ON c.UID_Org = r.UID_AERole101JOIN BaseTree rp102  ON rp.UID_Org = c.UID_ParentOrg103JOIN #GroupsAvailable gv104  ON rp.UID_DialogGroup = gv.UID_DialogGroup105JOIN PersonInAERole p106  ON p.UID_AERole = c.UID_Org AND p.UID_Person = @UID_Person) td107JOIN(108SELECT109  UID_Org,110  max(x.treelevel) AS maxLevel111FROM(112SELECT113  rp.UID_Org AS UID_AERole, c.UID_Org, rp.UID_DialogGroup, rp.treelevel114FROM AERole r115JOIN BaseTreeCollectionF c116  ON c.UID_Org = r.UID_AERole117JOIN BaseTree rp118  ON rp.UID_Org = c.UID_ParentOrg119JOIN #GroupsAvailable gv120  ON rp.UID_DialogGroup = gv.UID_DialogGroup121JOIN PersonInBaseTree p122  ON p.UID_Org = c.UID_Org AND p.UID_Person = @UID_Person) x123GROUP BY UID_Org) v124  ON v.UID_Org = td.UID_Org AND v.maxLevel = td.treelevel125IF @DebugSwitch > 0126BEGIN127  SELECT *128  FROM #GroupsForPerson129END130DELETE #GroupsForPerson131FROM #GroupsForPerson g132WHERE133  EXISTS(134SELECT TOP 1 1135FROM DialogGroupCollection co136WHERE137  g.UID_SingleGuid = co.UID_DialogGroupParent AND co.UID_DialogGroup <> co.UID_DialogGroupParent AND co.UID_DialogGroup IN(138SELECT e.UID_SingleGuid139FROM #GroupsForPerson e)) AND g.UID_SingleGuid <> @QBM_BaseRight140DELETE #GroupsForPerson141FROM #GroupsForPerson g142WHERE143  NOT EXISTS(144SELECT TOP 1 1145FROM DialogGroupCollection co146WHERE147  g.UID_SingleGuid = co.uid_dialogGroup) AND g.UID_SingleGuid <> @QBM_BaseRight148IF @DebugSwitch > 0149BEGIN150  SELECT *151  FROM #GroupsForPerson152END153IF NOT EXISTS(154  SELECT TOP 1 1155  FROM #GroupsForPerson g156  WHERE157    g.UID_SingleGUID = @QBM_BaseRight)158BEGIN159  INSERT INTO #GroupsForPerson(UID_SingleGUID)160  SELECT @QBM_BaseRight161END162SELECT @AnzahlGruppen = count(*)163FROM #GroupsForPerson164IF @AnzahlGruppen = 0165BEGIN166  RAISERROR('#LDS#No valid group passed.|',167  18,168  2)169    WITH nowait170  RETURN171END172IF @DebugSwitch > 0173BEGIN174  print 'Anzahl Gruppen ' + str(@AnzahlGruppen)175END176SELECT @UID_DialogUser = NULL177SELECT TOP 1 @UID_DialogUser = y.UID_DialogUser178FROM(179SELECT x.UID_DialogUser180FROM(181SELECT uig.UID_DialogUser182FROM #GroupsForPerson g183JOIN DialogUserInGroup uig184  ON uig.uid_DialogGroup = g.UID_SingleGuid185GROUP BY uig.UID_DialogUser186HAVING count(*) = @AnzahlGruppen187UNION all188SELECT uig.UID_DialogUser189FROM DialogUserInGroup uig190GROUP BY uig.UID_DialogUser191HAVING count(*) = @AnzahlGruppen) AS x192GROUP BY x.UID_DialogUser193HAVING count(*) = 2) AS y194JOIN DialogUser u195  ON y.UID_DialogUser = u.UID_DialogUser196WHERE197  u.isDynamicUser = 1198IF @UID_DialogUser > ' '199BEGIN200  IF @DebugSwitch > 0201  BEGIN202    print 'fertigen User gefunden'203  END204  GOTO fertig205END206IF @DebugSwitch > 0207BEGIN208  print '-- dann müssen wir einen anlegen'209END210SELECT @UID_DialogUser = newid()211IF EXISTS(212  SELECT TOP 1 1213  FROM DialogTable t214  WHERE215    t.TableName = 'DialogUser' AND t.IsModuleGUIDDefault = 1)216BEGIN217  SELECT218    @UID_DialogUser = dbo.QBM_FCVGuidToTransfer(@UID_DialogUser,219    'CCC')220END221EXEC QBM_PSessionContextSet 'GenProcID',222  @GenProcID223EXEC QBM_PSessionContextSet 'XUser',224  @XUser225IF @DebugSwitch > 0226BEGIN227  print 'vor insert user ' + str(DATEDIFF(ms, @Startdate, getutcdate()))228END229INSERT INTO DialogUser(UID_DialogUser,230UserName,231XObjectKey,232Password,233XDateInserted,234XDateUpdated,235XUserInserted,236XUserUpdated,237CustomRemarks,238IsReadOnly,239IsDynamicUser)240SELECT241  @UID_DialogUser,242  @UID_DialogUser,243  dbo.QBM_FCVElementToObjectKey1('DialogUser',244  'UID_DialogUser',245  @UID_DialogUser),246  'UnableForDialogLogin ',247  @XDate,248  @XDate,249  N 'System',250  @XUser_R,251  N 'Created by System',252  0,253  1254EXEC QBM_PSessionContextSet 'GenProcID',255  @GenProcID256EXEC QBM_PSessionContextSet 'XUser',257  @XUser_R258IF @DebugSwitch > 0259BEGIN260  print 'vor insert useringroup ' + str(DATEDIFF(ms, @Startdate, getutcdate()))261END262INSERT INTO DialogUserInGroup(UID_DialogUser,263uid_DialogGroup,264XDateInserted,265XDateUpdated,266XUserInserted,267XUserUpdated,268XObjectKey)269SELECT270  DISTINCT @UID_DialogUser,271  g.UID_SingleGuid,272  @XDate,273  @XDate,274  N 'System',275  @XUser,276  dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup',277  'UID_DialogUser',278  @UID_DialogUser,279  'uid_DialogGroup',280  g.UID_SingleGuid)281FROM #GroupsForPerson g282WHERE283  NOT EXISTS(284SELECT TOP 1 1285FROM DialogUserInGroup e286WHERE287  e.UID_DialogUser = @UID_DialogUser AND e.UID_DialogGroup = g.UID_SingleGuid)288IF @DebugSwitch > 0289BEGIN290  print 'nach insert useringroup ' + str(DATEDIFF(ms, @Startdate, getutcdate()))291END292fertig:293UPDATE DialogUser294SET LastLogin = @XDate,295XDateUpdated = @Xdate,296XUserUpdated = @XUser297WHERE298  UID_DialogUser = @UID_DialogUser299IF @DebugSwitch > 0300BEGIN301  print 'nach update user ' + str(DATEDIFF(ms, @Startdate, getutcdate()))302END303END TRY304BEGIN CATCH305  EXEC QBM_PSessionErrorAdd DEFAULT306  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()307  RAISERROR(@Rethrow,308  18,309  1)310    WITH NOWAIT311END CATCH312EndLabel:313EXEC QBM_PSessionContextSet 'GenProcID',314  @GenProcID_R315EXEC QBM_PSessionContextSet 'XUser',316  @XUser_R317RETURN318END
Open raw exported source
SQL · Raw61 lines
1   create   procedure QBM_PDynamicUserForPerson (@UID_Person varchar(38) , @UID_QBMProduct varchar(38) , @UID_DialogUser varchar(38) output ) as2 begin declare @GenProcID varchar(38) declare @XUser nvarchar(64) = object_name(@@procid) declare @XDate datetime = getutcdate() declare @SQLCmd nvarchar3(max) declare @DebugSwitch int = 0 declare @Startdate datetime = getutcdate() declare @UID_QBMProduct_Intern varchar(38) = isnull(@UID_QBMProduct, '##'4)   drop table if exists #GroupsAvailable create table #GroupsAvailable (UID_DialogGroup varchar(38) collate database_default primary key (UID_DialogGroup5) ) drop table if exists #GroupsForPerson create table #GroupsForPerson (UID_SingleGUID varchar(38) collate database_default primary key (UID_SingleGUID6) ) declare @anzahlGruppen int declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext7('XUser') declare @QBM_BaseRight varchar(38) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5' SET XACT_ABORT OFF BEGIN TRY set nocount on select @GenProcID = @GenProcID_R8  select @SQLCmd = concat(  'select g.UID_DialogGroup-- , gl.*9			from DialogGroup g left outer join DialogGroupInProductLimited gl on g.UID_DialogGroup = gl.UID_DialogGroup10			where gl.UID_DialogGroup is null11			-- reduktion um die Gruppen, die für Rolebased zur Verfügung stehen'12 , case when dbo.QBM_FGIModuleExists('QER') = 1 then '13						and g.IsRoleBasedOnly = 1' else '' end, '14		union1516		-- die Gruppen für dieses Produkt17		select gl.UID_DialogGroup18			from DialogGroupInProductLimited gl join DialogGroup g on gl.UID_DialogGroup = g.UID_DialogGroup19			where gl.UID_DialogProduct = '''20, @UID_QBMProduct_Intern , '''21			-- reduktion um die Gruppen, die für Rolebased zur Verfügung stehen' , case when dbo.QBM_FGIModuleExists('QER') = 1 22then '23						and g.IsRoleBasedOnly = 1' else '' end, '24		union2526		select ''', @QBM_BaseRight, '''27		' )  if @DebugSwitch > 0 begin print @sqlcmd end28 insert into #GroupsAvailable(UID_DialogGroup) exec sp_executesql @SQLCmd if @DebugSwitch > 0 begin select * from #GroupsAvailable end insert into #GroupsForPerson29(UID_SingleGuid) select distinct UID_DialogGroup from ( select rp.UID_Org as UID_AERole, c.UID_Org, rp.UID_DialogGroup, rp.treelevel from AERole r join30 BaseTreeCollectionF c on c.UID_Org = r.UID_AERole join BaseTree  rp on rp.UID_Org = c.UID_ParentOrg join #GroupsAvailable gv on rp.UID_DialogGroup = gv.UID_DialogGroup31 join PersonInAERole p on p.UID_AERole = c.UID_Org and p.UID_Person = @UID_Person ) td join ( select UID_Org, max(x.treelevel) as maxLevel from ( select32 rp.UID_Org as UID_AERole, c.UID_Org, rp.UID_DialogGroup, rp.treelevel from AERole r join BaseTreeCollectionF c on c.UID_Org = r.UID_AERole join BaseTree33  rp on rp.UID_Org = c.UID_ParentOrg join #GroupsAvailable gv on rp.UID_DialogGroup = gv.UID_DialogGroup join PersonInBaseTree  p on p.UID_Org = c.UID_Org34 and p.UID_Person = @UID_Person ) x group by UID_Org ) v on v.UID_Org = td.UID_Org and v.maxLevel = td.treelevel if @DebugSwitch > 0 begin select * from35 #GroupsForPerson end  delete  #GroupsForPerson  from #GroupsForPerson g where exists (select top 1 1 from DialogGroupCollection co where g.UID_SingleGuid36  = co.UID_DialogGroupParent and co.UID_DialogGroup <> co.UID_DialogGroupParent and co.UID_DialogGroup in (select e.UID_SingleGuid from #GroupsForPerson37 e ) ) and g.UID_SingleGuid  <> @QBM_BaseRight delete  #GroupsForPerson  from #GroupsForPerson g where Not exists (select top 1 1 from DialogGroupCollection38 co where g.UID_SingleGuid  = co.uid_dialogGroup ) and g.UID_SingleGuid  <> @QBM_BaseRight  if @DebugSwitch > 0 begin select * from #GroupsForPerson end39 if not exists (select top 1 1 from #GroupsForPerson g where g.UID_SingleGUID = @QBM_BaseRight ) begin insert into #GroupsForPerson(UID_SingleGUID) select40 @QBM_BaseRight end select @AnzahlGruppen = count(*) from #GroupsForPerson if @AnzahlGruppen = 0 begin  raiserror('#LDS#No valid group passed.|' , 18, 412) with nowait return end if @DebugSwitch > 0 begin print 'Anzahl Gruppen ' + str(@AnzahlGruppen) end select @UID_DialogUser = null select top 1 @UID_DialogUser42 = y.UID_DialogUser from ( select x.UID_DialogUser from (  select uig.UID_DialogUser from #GroupsForPerson g join DialogUserInGroup uig on uig.uid_DialogGroup43 = g.UID_SingleGuid  group by uig.UID_DialogUser having count(*) = @AnzahlGruppen union all  select uig.UID_DialogUser from DialogUserInGroup uig group44 by uig.UID_DialogUser having count(*) = @AnzahlGruppen ) as x group by x.UID_DialogUser having count(*) = 2 ) as y join DialogUser u on y.UID_DialogUser45 = u.UID_DialogUser where u.isDynamicUser = 1  if @UID_DialogUser > ' ' begin if @DebugSwitch > 0 begin print 'fertigen User gefunden' end goto fertig 46end if @DebugSwitch > 0 begin print '-- dann müssen wir einen anlegen' end select @UID_DialogUser = newid()   if exists (select top 1 1 from DialogTable47 t where t.TableName = 'DialogUser' and t.IsModuleGUIDDefault = 1 ) begin select @UID_DialogUser = dbo.QBM_FCVGuidToTransfer(@UID_DialogUser, 'CCC') end48  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser if @DebugSwitch > 0 begin print 'vor insert user ' + 49str(DATEDIFF(ms, @Startdate, getutcdate())) end insert into DialogUser (UID_DialogUser, UserName, XObjectKey, Password, XDateInserted, XDateUpdated, XUserInserted50, XUserUpdated, CustomRemarks , IsReadOnly, IsDynamicUser ) select @UID_DialogUser, @UID_DialogUser, dbo.QBM_FCVElementToObjectKey1('DialogUser', 'UID_DialogUser'51, @UID_DialogUser), 'UnableForDialogLogin ', @XDate, @XDate, N'System', @XUser_R, N'Created by System' , 0, 1 exec QBM_PSessionContextSet 'GenProcID', 52@GenProcID exec QBM_PSessionContextSet 'XUser', @XUser_R if @DebugSwitch > 0 begin print 'vor insert useringroup ' + str(DATEDIFF(ms, @Startdate, getutcdate53())) end insert into DialogUserInGroup (UID_DialogUser , uid_DialogGroup , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated , XObjectKey) select54 distinct @UID_DialogUser, g.UID_SingleGuid , @XDate, @XDate, N'System', @XUser, dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup', 'UID_DialogUser', 55@UID_DialogUser, 'uid_DialogGroup', g.UID_SingleGuid ) from #GroupsForPerson g  where not exists (select top 1 1 from DialogUserInGroup e where e.UID_DialogUser56 = @UID_DialogUser and e.UID_DialogGroup = g.UID_SingleGuid  ) if @DebugSwitch > 0 begin print 'nach insert useringroup ' + str(DATEDIFF(ms, @Startdate57, getutcdate())) end fertig: update DialogUser set LastLogin = @XDate  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_DialogUser = @UID_DialogUser58 if @DebugSwitch > 0 begin print 'nach update user ' + str(DATEDIFF(ms, @Startdate, getutcdate())) end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default59 declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  EndLabel: exec QBM_PSessionContextSet60 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 61