Back to OIM Explorer

dbo.CPL_PRuleCompareWorkForReport

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.948 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_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL69 lines
1CREATE PROCEDURE CPL_PRuleCompareWorkForReport(2  @UID_ComplianceRuleWork varchar(38)3)4AS5BEGIN6  DECLARE @whereOld nvarchar(max) = '1 = 0'7  DECLARE @whereNew nvarchar(max) = '1 = 0'8  DECLARE @selectList nvarchar(1000) = 'select Internalname, DefaultEmailAddress as Mail, Phone, UID_Locality, UID_Department, UID_ProfitCenter, '9  DECLARE @DebugSwitch int = 010  DECLARE @SQLcmd nvarchar(max)11  SET XACT_ABORT OFF12  BEGIN TRY13    SELECT14      TOP 1 @whereOld = isnull(r.whereclause,15      '1 = 0'),16      @whereNew = isnull(w.whereclause,17      '1 = 0')18    FROM ComplianceRule r19    JOIN ComplianceRule w20      ON w.UID_ComplianceRule = r.UID_ComplianceRuleWork21    WHERE22      w.uid_compliancerule = @UID_ComplianceRuleWork23    SELECT24      @SQLcmd = 'select x.Internalname, x.Mail, x.Phone, p.Shortname as ProfitCenter, d.Departmentname as Department, l.Ident_locality as Locality, x.Comparison 25		from ('26      + @selectList + '''EQUAL'' as Comparison, 3 as SortOrder 27				from person 28				where (' + @whereOld + ') 29				 and (' + @whereNew + ') 3031			union all 3233			'34      + @selectList + '''DEL'', 2 35			 from person 36			 where (' + @whereOld + ') 37			 and not (' + @whereNew + ')  3839			union all 40			41			' + @selectList42      + '''NEW'', 1 43			from person 44			where not (' + @whereOld + ') 45			and (' + @whereNew + ') 46		) as x left outer join department d on d.uid_department = x.uid_department 47				left outer join locality l on l.uid_locality = x.uid_locality 48				left outer join ProfitCenter p on p.uid_ProfitCenter = x.uid_ProfitCenter 49	order by x.SortOrder, x.Internalname'50    IF @DebugSwitch > 051    BEGIN52      print @sqlcmd53    END54    EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,55      @LockTimeout_ms = DEFAULT,56      @MaxWaitTimeForLock_s = DEFAULT,57      @ProcIDForJournal = @@procid,58      @HandleErrorSilent = 059  END TRY60  BEGIN CATCH61    EXEC QBM_PSessionErrorAdd DEFAULT62    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()63    RAISERROR(@Rethrow,64    18,65    1)66      WITH NOWAIT67  END CATCH68  endLabel:69END
Open raw exported source
SQL ยท Raw34 lines
1  create   procedure CPL_PRuleCompareWorkForReport(@UID_ComplianceRuleWork varchar(38) ) as begin declare @whereOld nvarchar(max) = '1 = 0' declare2 @whereNew nvarchar(max) = '1 = 0' declare @selectList nvarchar(1000) = 'select Internalname, DefaultEmailAddress as Mail, Phone, UID_Locality, UID_Department, UID_ProfitCenter, '3 declare @DebugSwitch int = 0 declare @SQLcmd nvarchar(max) SET XACT_ABORT OFF BEGIN TRY select top 1 @whereOld = isnull(r.whereclause, '1 = 0') , @whereNew4 = isnull(w.whereclause, '1 = 0') from ComplianceRule r join ComplianceRule w on w.UID_ComplianceRule = r.UID_ComplianceRuleWork  where w.uid_compliancerule5 = @UID_ComplianceRuleWork select @SQLcmd = 'select x.Internalname, x.Mail, x.Phone, p.Shortname as ProfitCenter, d.Departmentname as Department, l.Ident_locality as Locality, x.Comparison 6		from ('7 + @selectList + '''EQUAL'' as Comparison, 3 as SortOrder 8				from person 9				where (' + @whereOld + ') 10				 and (' + @whereNew + ') 1112			union all 1314			'15 + @selectList + '''DEL'', 2 16			 from person 17			 where (' + @whereOld + ') 18			 and not (' + @whereNew + ')  1920			union all 21			22			' + @selectList23 + '''NEW'', 1 24			from person 25			where not (' + @whereOld + ') 26			and (' + @whereNew + ') 27		) as x left outer join department d on d.uid_department = x.uid_department 28				left outer join locality l on l.uid_locality = x.uid_locality 29				left outer join ProfitCenter p on p.uid_ProfitCenter = x.uid_ProfitCenter 30	order by x.SortOrder, x.Internalname'31 if @DebugSwitch > 0 begin print @sqlcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s32 = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000)33 = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: end 34