Back to OIM Explorer

dbo.TSB_PMapAccountToPerson

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.578 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_FSQTriggerWatchDisplay source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

dbo.QBM_FTSQLKeywords

Complete Source

1CREATE PROCEDURE TSB_PMapAccountToPerson(2  @AccountTable varchar(30),3  @JoinClause nvarchar(max),4  @WhereClause nvarchar(max)5)6AS7BEGIN8  DECLARE @cmd nvarchar(max)9  DECLARE @DebugSwitch int = 010  SET XACT_ABORT OFF11  BEGIN TRY12    DECLARE @displayAccount nvarchar(max)13    DECLARE @displayPerson nvarchar(max)14    DECLARE @UID_DialogTableAccount varchar(38)15    SELECT16      TOP 1 @displayAccount = t.DisplayPattern,17      @UID_DialogTableAccount = t.UID_DialogTable18    FROM DialogTable t19    WHERE20      t.TableName = @AccountTable21    IF @DebugSwitch > 022    BEGIN23      print '@displayAccount ' + @displayAccount print '@UID_DialogTableAccount ' + @UID_DialogTableAccount24    END25    SELECT26      @displayAccount = dbo.QBM_FSQTriggerWatchDisplay(@AccountTable,27      'a')28    SELECT29      @displayPerson = dbo.QBM_FSQTriggerWatchDisplay('Person',30      'p')31    IF @DebugSwitch > 032    BEGIN33      print @displayAccount34    END35    SELECT36      TOP 1 @cmd = CONCAT('37select 38	p.UID_Person as UID_Person, 39	',40      @displayPerson,41      ' as InternalName, 42	a.',43      t.PKName1,44      ' as UID_AccountTable, 45	',46      @displayAccount,47      ' as DisplayAccount, 48	',49    CASE50      WHEN ta.UID_DialogTableContainer IS NULL THEN51    'null'52    ELSE 'c.' + tc.PKName153    END,54    ' as UID_ContainerTable, 55	',56    CASE57      WHEN ta.UID_DialogTableContainer IS NULL THEN58    'null'59    ELSE dbo.QBM_FSQTriggerWatchDisplay(tc.TableName, 'c')60    END,61    ' as DisplayContainer, 62	a.',63    r.ChildColumn,64    ' as TSBAccountDefColumnName, 65	a.XMarkedForDeletion, 66	',67    CASE68      WHEN ta.ColumnNameAccDisabled IS NULL THEN69    'null'70    ELSE 'a.' + ta.ColumnNameAccDisabled71    END,72    ' as AccountIsDisabled, 73	p.IsInactive as PersonIsInactive74						   from ',75    @AccountTable,76    ' a 77									left outer join Person p on ',78    @JoinClause,79    '80									',81    CASE82      WHEN ta.UID_DialogTableContainer IS NULL THEN83    ''84    ELSE CONCAT('left outer join ', tc.TableName, ' c on a.', ta.ColumnNameContainerFK, ' = c.', tc.PKName1,85    '')86    END,87    '88						  where ',89    @WhereClause,90    ' and NeverConnectToPerson = 091						  order by 6,5,4,392	')93    FROM DialogTable t94    JOIN QBM_VQBMRelation r95      ON r.UID_DialogTableChild = t.UID_DialogTable96    JOIN DialogTable tsba97      ON r.UID_DialogTableParent = 'TSB-T-TSBAccountDef'98    LEFT99    OUTER100    JOIN TSBVAccountTable ta101      ON ta.TableNameAccount = @AccountTable102    LEFT103    OUTER104    JOIN DialogTable tc105      ON ta.UID_DialogTableContainer = tc.UID_DialogTable106    LEFT107    OUTER108    JOIN DialogColumn cc109      ON replace(tc.DisplayPattern,110    '??',111    '%%') LIKE '%[%]' + cc.columnname + N '[%]%'112    WHERE113      t.TableName = @AccountTable114    IF @DebugSwitch > 0115    BEGIN116      print @cmd117    END118    EXEC sp_executesql @cmd119  END TRY120  BEGIN CATCH121    EXEC QBM_PSessionErrorAdd DEFAULT122    RAISERROR('',123    18,124    1)125      WITH NOWAIT126  END CATCH127END

Open raw exported source

1       create   procedure TSB_PMapAccountToPerson (@AccountTable varchar(30) , @JoinClause nvarchar(max) , @WhereClause nvarchar(max) ) as begin2 declare @cmd nvarchar(max) declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY    declare @displayAccount nvarchar(max) declare @displayPerson nvarchar3(max) declare @UID_DialogTableAccount varchar(38) select top 1 @displayAccount = t.DisplayPattern , @UID_DialogTableAccount = t.UID_DialogTable from DialogTable4 t where t.TableName = @AccountTable if @DebugSwitch > 0 begin print '@displayAccount ' + @displayAccount print '@UID_DialogTableAccount ' + @UID_DialogTableAccount5 end select @displayAccount = dbo.QBM_FSQTriggerWatchDisplay(@AccountTable, 'a') select @displayPerson = dbo.QBM_FSQTriggerWatchDisplay('Person', 'p') 6if @DebugSwitch > 0 begin print @displayAccount end select top 1 @cmd = concat('7select 8	p.UID_Person as UID_Person, 9	', @displayPerson , ' as InternalName, 10	a.'11, t.PKName1 , ' as UID_AccountTable, 12	',  @displayAccount ,' as DisplayAccount, 13	', case when ta.UID_DialogTableContainer is null then 'null' else 14'c.' + tc.PKName1 end, ' as UID_ContainerTable, 15	',  case when ta.UID_DialogTableContainer is null then 'null' else dbo.QBM_FSQTriggerWatchDisplay(tc.TableName16, 'c')  end, ' as DisplayContainer, 17	a.', r.ChildColumn ,' as TSBAccountDefColumnName, 18	a.XMarkedForDeletion, 19	', case when ta.ColumnNameAccDisabled20 is null then 'null' else 'a.'+ ta.ColumnNameAccDisabled end, ' as AccountIsDisabled, 21	p.IsInactive as PersonIsInactive22						   from ', @AccountTable23 , ' a 24									left outer join Person p on ', @JoinClause , '25									' , case when ta.UID_DialogTableContainer is null then '' else concat('left outer join '26, tc.TableName , ' c on a.', ta.ColumnNameContainerFK, ' = c.' , tc.PKName1 ,'') end, '27						  where ', @WhereClause , ' and NeverConnectToPerson = 028						  order by 6,5,4,329	'30 ) from DialogTable t join QBM_VQBMRelation r on r.UID_DialogTableChild = t.UID_DialogTable join DialogTable tsba on r.UID_DialogTableParent = 'TSB-T-TSBAccountDef'31 left outer join TSBVAccountTable ta on ta.TableNameAccount = @AccountTable left outer join DialogTable tc on ta.UID_DialogTableContainer = tc.UID_DialogTable32 left outer join DialogColumn cc on replace(tc.DisplayPattern, '??', '%%') like '%[%]' + cc.columnname + N'[%]%' where t.TableName = @AccountTable if @DebugSwitch33 > 0 begin print @cmd end exec sp_executesql @cmd END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end34 35