Back to OIM Explorer

dbo.QER_ZPersonFindGenerateFunct

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.316 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_FCVGUIDToModuleOwner source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIDBOwner source text reference
  • references source dbo.QBM_FSQRemoveComment source text reference
  • references source dbo.QBM_FSQRemoveComment_i source text reference
  • references source dbo.QBM_PDBQueueCurrentResetNGen source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_FCVStringToList_i source text reference
  • references source dbo.QER_FTPersonFindByTSProp source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL145 lines
1CREATE PROCEDURE QER_ZPersonFindGenerateFunct(2  @SlotNumberDummy int,3  @dummy1 varchar(38),4  @dummy2 varchar(38),5  @dummyGenProcID varchar(38)6)7AS8BEGIN9  DECLARE @DisabledBy varchar(1024) = dbo.QBM_FGIConfigparmValue('QER\Person\PasswordResetAuthenticator\DisabledBy')10  DECLARE @EnabledBy varchar(1024) = dbo.QBM_FGIConfigparmValue('QER\Person\PasswordResetAuthenticator\EnabledBy')11  DECLARE @SearchColumn varchar(1024) = dbo.QBM_FGIConfigparmValue('QER\Person\PasswordResetAuthenticator\SearchColumn')12  DECLARE @SearchTable varchar(30) = dbo.QBM_FGIConfigparmValue('QER\Person\PasswordResetAuthenticator\SearchTable')13  DECLARE @DisabledConverted nvarchar(max)14  DECLARE @EnabledConverted nvarchar(max)15  DECLARE @PersonColumnName varchar(30)16  DECLARE @FunctionName varchar(30) = 'QER_FTPersonFindByTSProp'17  DECLARE @FunctionBody nvarchar(max)18  DECLARE @header nvarchar(max) = 'create or alter function dbo.QER_FTPersonFindByTSProp( @CompareValue nvarchar(400) 19											)20	returns @erg table (UID_Person varchar(38) 21						)22as 23begin24	insert into @erg(UID_Person)25'26  DECLARE @footer nvarchar(max) = '2728return2930end3132'33  DECLARE @DebugSwitch int = 034  DECLARE @ResetMessage nvarchar(4000)35  BEGIN TRY36    IF @DebugSwitch > 037    BEGIN38      SELECT @SearchColumn = 'cn|DistinguishedName|GivenName'39      SELECT @SearchTable = 'ADSAccount'40      SELECT @DisabledBy = 'AllowDialIn|IsGroupAccount'41    END42    SELECT TOP 1 @PersonColumnName = r.ChildColumn43    FROM QBM_VQBMRelation r44    JOIN QBMModuleDef d45      ON dbo.QBM_FCVGUIDToModuleOwner(r.UID_QBMRelation) = d.ModuleName46    WHERE47      r.ChildTable = @SearchTable AND r.ParentTable = 'Person'48    ORDER BY d.SortOrder ASC49    IF @SearchTable = 'Person'50    BEGIN51      SELECT @PersonColumnName = 'UID_Person'52    END53    SELECT54      @DisabledConverted = CONCAT('55	-- disabled56	and ',57      replace(@DisabledBy, '|', ' = 0 and '),58      ' = 0')59    SELECT60      @EnabledConverted = CONCAT('61	-- enabled62	and ',63      replace(@EnabledBy, '|', ' = 1 and '),64      ' = 1')65    IF @SearchColumn > ' ' AND @SearchTable > ' '66    BEGIN67      SELECT68        @FunctionBody = CONCAT(@header,69        '70	select ',71        @PersonColumnName,72        ' 73	from ',74        @SearchTable,75        ' 76	where ',77        (78      SELECT79        CONCAT('(', String_agg(CONCAT(s.Parameter1, ' = @CompareValue '), ' or '), ')')80      FROM dbo.QBM_FCVStringToList_i(@SearchColumn, '|', 1, 0, '') s),81      '  82		',83      CASE84        WHEN @EnabledBy > ' ' THEN85      @EnabledConverted86      ELSE ''87      END,88      CASE89        WHEN @DisabledBy > ' ' THEN90      @DisabledConverted91      ELSE ''92      END,93      @footer)94    END95    ELSE96    BEGIN97      SELECT98        @FunctionBody = CONCAT(@header,99        ' select p.UID_Person100	from Person p101	where p.CentralAccount = @CompareValue102103	',104        @footer)105    END106    IF @DebugSwitch > 0107    BEGIN108      print @functionbody109    END110    BEGIN TRY111      IF dbo.QBM_FGIDBOwner() = 'CCC'112      BEGIN113        SELECT114          @FunctionBody = dbo.QBM_FSQRemoveComment_i(@FunctionBody,115          1)116      END117      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @FunctionBody,118        @LockTimeout_ms = DEFAULT,119        @MaxWaitTimeForLock_s = DEFAULT,120        @ProcIDForJournal = DEFAULT,121        @HandleErrorSilent = 0,122        @ExecuteWithTransact = 0123    END TRY124    BEGIN CATCH125      EXEC QBM_PSessionErrorAdd DEFAULT126      SELECT @ResetMessage = ERROR_MESSAGE()127      IF @@TRANCOUNT > 0128      BEGIN129        ROLLBACK130      END131      EXEC QBM_PDBQueueCurrentResetNGen @SlotNumberDummy,132        @ResetMessage,133        @@PROCID134    END CATCH135  END TRY136  BEGIN CATCH137    EXEC QBM_PSessionErrorAdd DEFAULT138    RAISERROR('',139    18,140    1)141      WITH NOWAIT142  END CATCH143  ende:144  RETURN145END
Open raw exported source
SQL ยท Raw46 lines
1    create   procedure QER_ZPersonFindGenerateFunct (@SlotNumberDummy int , @dummy1 varchar(38) , @dummy2 varchar(38) , @dummyGenProcID varchar(382) ) as begin declare @DisabledBy varchar(1024) = dbo.QBM_FGIConfigparmValue('QER\Person\PasswordResetAuthenticator\DisabledBy') declare @EnabledBy varchar3(1024) = dbo.QBM_FGIConfigparmValue('QER\Person\PasswordResetAuthenticator\EnabledBy') declare @SearchColumn varchar(1024) = dbo.QBM_FGIConfigparmValue4('QER\Person\PasswordResetAuthenticator\SearchColumn') declare @SearchTable varchar(30) = dbo.QBM_FGIConfigparmValue('QER\Person\PasswordResetAuthenticator\SearchTable'5) declare @DisabledConverted nvarchar(max) declare @EnabledConverted nvarchar(max) declare @PersonColumnName varchar(30) declare @FunctionName varchar(306) = 'QER_FTPersonFindByTSProp' declare @FunctionBody nvarchar(max) declare @header nvarchar(max) = 'create or alter function dbo.QER_FTPersonFindByTSProp( @CompareValue nvarchar(400) 7											)8	returns @erg table (UID_Person varchar(38) 9						)10as 11begin12	insert into @erg(UID_Person)13'14 declare @footer nvarchar(max) = '1516return1718end1920' declare @DebugSwitch int = 0 declare @ResetMessage nvarchar(4000) BEGIN TRY if @DebugSwitch > 210 begin select @SearchColumn = 'cn|DistinguishedName|GivenName' select @SearchTable = 'ADSAccount' select @DisabledBy = 'AllowDialIn|IsGroupAccount'   22    end select top 1 @PersonColumnName = r.ChildColumn from QBM_VQBMRelation r join QBMModuleDef d on dbo.QBM_FCVGUIDToModuleOwner(r.UID_QBMRelation) =23 d.ModuleName where r.ChildTable = @SearchTable and r.ParentTable = 'Person'  order by d.SortOrder asc if @SearchTable = 'Person' begin select @PersonColumnName24 = 'UID_Person' end  select @DisabledConverted =  concat('25	-- disabled26	and ' , replace(@DisabledBy, '|', ' = 0 and ') , ' = 0' ) select @EnabledConverted27 =  concat('28	-- enabled29	and ' , replace(@EnabledBy, '|', ' = 1 and ') , ' = 1' )  if @SearchColumn > ' ' and @SearchTable > ' ' begin select @FunctionBody30 = concat( @header, '31	select ', @PersonColumnName , ' 32	from ', @SearchTable , ' 33	where ', ( select concat( '(', String_agg(concat(s.Parameter1 , 34' = @CompareValue ') , ' or '), ')' ) from dbo.QBM_FCVStringToList_i(@SearchColumn, '|', 1, 0, '') s ), '  35		', case  when @EnabledBy > ' ' then @EnabledConverted36 else '' end , case when @DisabledBy > ' ' then @DisabledConverted else '' end , @footer )  end else begin  select @FunctionBody = concat(@header, ' select p.UID_Person37	from Person p38	where p.CentralAccount = @CompareValue3940	'41, @footer ) end if @DebugSwitch > 0 begin print @functionbody end BEGIN TRY    if dbo.QBM_FGIDBOwner() = 'CCC' begin select @FunctionBody = dbo.QBM_FSQRemoveComment_i42(@FunctionBody, 1) end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @FunctionBody , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , 43@ProcIDForJournal = default , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default select @ResetMessage44 = ERROR_MESSAGE() if @@TRANCOUNT > 0 begin rollback end  exec QBM_PDBQueueCurrentResetNGen @SlotNumberDummy , @ResetMessage , @@PROCID END CATCH END TRY45 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 46