Back to OIM Explorer

dbo.QER_ZEntitlementSourceMakeProc

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 8.438 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_FCVStringToIndent source text reference
  • references source dbo.QBM_FGICodeName 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.QER_FTEntitlementSourceWhat source text reference
  • references source dbo.QER_FTEntitlementSourceWho source text reference
  • references source dbo.QER_FTEntitlementSourceWhy source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL315 lines
1CREATE PROCEDURE QER_ZEntitlementSourceMakeProc(2  @SlotNumber int,3  @dummy1 varchar(38),4  @Dummy2 varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @DebugSwitch int = 010  DECLARE @FunctionNameWho nvarchar(64) = 'QER_FTEntitlementSourceWho'11  DECLARE @FunctionNameWhy nvarchar(64) = 'QER_FTEntitlementSourceWhy'12  DECLARE @FunctionNameWhat nvarchar(64) = 'QER_FTEntitlementSourceWhat'13  DECLARE @HeaderWho nvarchar(max) = CONCAT('create or alter function dbo.',14  @FunctionNameWho,15  '(16											@ObjectKeyToInspect varchar(138)17											)18	returns @erg table (19						UID_Person varchar(38) collate database_default20						)21 as22begin23'24  )25  DECLARE @SubBodyWho nvarchar(max) = '26	if @ObjectKeyToInspect like ''<Key><T>@TableName@</T>%''27	 begin28	  insert into @erg(uid_person)29	  select /*distinct */ xx.UID_Person30	   from dbo.@FunctionWhoName (@ObjectKeyToInspect) as xx3132	   goto EndLabel3334	 end -- @TableName@35'36  DECLARE @FooterWho nvarchar(max) = '37	--insert into @erg(uid_person)38	--select distinct UID_Person39	-- from @work40 endLabel:4142 return43end44'45  DECLARE @FunctionWhoPattern nvarchar(max) = '46create or alter function dbo.@FunctionWhoName (@ObjectKeyToInspect varchar(138)47									)48returns table49 50as return(51	  select distinct xx.UID_Person52	   from (53@CustomerCode54			) as xx55	   where xx.ObjectKeyToInspect = @ObjectKeyToInspect56)57'58  DECLARE @FunctionWhoName varchar(30)59  DECLARE @HeaderWhy nvarchar(max) = CONCAT('create or alter function dbo.',60  @FunctionNameWhy,61  '(62											@UID_Person varchar(38) 63											, @ObjectKeyToInspect varchar(138)64											)65	returns @erg table (66						ObjectKeyReason1 varchar(138) collate database_default67						, ObjectKeyReason2 varchar(138) collate database_default68						)69 as70begin71'72  )73  DECLARE @SubBodyWhy nvarchar(max) = '74	if @ObjectKeyToInspect like ''<Key><T>@TableName@</T>%''75	 begin76	  insert into @erg(ObjectKeyReason1, ObjectKeyReason2)77	  select distinct xx.ObjectKeyReason1, xx.ObjectKeyReason278	   from dbo.@FunctionWhyName (@uid_person, @ObjectKeyToInspect) as xx7980	   goto EndLabel8182	 end -- @TableName@83'84  DECLARE @FooterWhy nvarchar(max) = '85	--insert into @erg(ObjectKeyReason1, ObjectKeyReason2)86	--select distinct ObjectKeyReason1, ObjectKeyReason287	-- from @work8889	EndLabel:9091 return92end93'94  DECLARE @FunctionWhyPattern nvarchar(max) = '95create or alter function dbo.@FunctionWhyName (@UID_Person varchar(38)96								, @ObjectKeyToInspect varchar(138)97									)98returns table99 100as return(101        select xx.ObjectKeyReason1, xx.ObjectKeyReason2102		from (                                                                                    103@CustomerCode			104					) as xx105        where (xx.UID_Person = @UID_Person106                  or (xx.UID_Person is null 107                      )108               )109         and xx.ObjectKeyToInspect = @ObjectKeyToInspect110)111'112  DECLARE @FunctionWhyName varchar(30)113  DECLARE @HeaderWhat nvarchar(max) = CONCAT('create or alter function dbo.',114  @FunctionNameWhat,115  '(116											@UID_Person varchar(38) 117											)118	returns @erg table (119						ObjectKeyToInspect varchar(138) collate database_default120						)121 as122begin123'124  )125  DECLARE @SubBodyWhat nvarchar(max) = '126	  insert into @work(ObjectKeyToInspect)127	  select xx.ObjectKeyToInspect128	   from dbo.@FunctionWhatName (@UID_Person) as xx129'130  DECLARE @FooterWhat nvarchar(max) = '131	insert into @erg(ObjectKeyToInspect)132	select distinct ObjectKeyToInspect133	 from @work134 return135end136'137  DECLARE @FunctionWhatPattern nvarchar(max) = '138create or alter function dbo.@FunctionWhatName (@UID_Person varchar(38)139									)140returns table141 142as return(143	  select xx.ObjectKeyToInspect144	   from (145@CustomerCode146			) as xx147	   where xx.UID_Person = @UID_Person148)149'150  DECLARE @FunctionWhatName varchar(30)151  DECLARE @Declares nvarchar(max) = '152declare @work QER_YEntitlementSource153'154  DECLARE @KlammerAuf nvarchar(max) = '155select 156	xx.UID_Person as UID_Person157	, xx.ObjectKeyToInspect as ObjectKeyToInspect158	, xx.ObjectKeyReason1 as ObjectKeyReason1159	, xx.ObjectKeyReason2 as ObjectKeyReason2160	from (161'162  DECLARE @KlammerZu nvarchar(max) = '163	) as xx164'165  DECLARE @cmdWho nvarchar(max) = ''166  DECLARE @cmdWhy nvarchar(max) = ''167  DECLARE @cmdWhat nvarchar(max) = ''168  DECLARE @SQLQuery nvarchar(max)169  DECLARE @Ident_QEREntitlementSource nvarchar(64)170  DECLARE @TableName varchar(30)171  DECLARE @ElementBuffer QBM_YCursorBuffer172  DECLARE @ElementCount int173  DECLARE @ElementIndex int174  DECLARE @FunctionDef nvarchar(max)175  DECLARE @ResetMessage nvarchar(4000)176  BEGIN TRY177    INSERT INTO @ElementBuffer(ContentFull,178    ContentShort,179    Ident1,180    UID1)181    SELECT182      STRING_AGG(CONCAT(@KlammerAuf, dbo.QBM_FCVStringToIndent(s.SQLQuery, 1), @KlammerZu),183      '184	union all 185	') AS SQLQuery,186      max(s.Ident_QEREntitlementSource) AS Ident_QEREntitlementSource,187      t.TableName,188      max(s.UID_QEREntitlementSource) AS UID_QEREntitlementSource189    FROM QEREntitlementSource s190    JOIN DialogTable t191      WITH(readpast)192      ON t.UID_DialogTable = s.UID_DialogTable193    GROUP BY t.TableName194    SELECT @ElementCount = @@ROWCOUNT195    SELECT @ElementIndex = 1196    WHILE @ElementIndex <= @ElementCount197    BEGIN198      SELECT199        TOP 1 @SQLQuery = bu.ContentFull,200        @Ident_QEREntitlementSource = bu.ContentShort,201        @TableName = bu.Ident1,202        @FunctionWhyName = dbo.QBM_FGICodeName('FTEY',203        bu.UID1),204        @FunctionWhoName = dbo.QBM_FGICodeName('FTEO',205        bu.UID1),206        @FunctionWhatName = dbo.QBM_FGICodeName('FTEA',207        bu.UID1)208      FROM @ElementBuffer bu209      WHERE210        bu.ElementIndex = @ElementIndex211      SELECT212        @FunctionDef = replace(REPLACE(@FunctionWhyPattern, '@CustomerCode', dbo.QBM_FCVStringToIndent(@SQLQuery,213        3)),214        '@FunctionWhyName',215        @FunctionWhyName)216      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @FunctionDef,217        @LockTimeout_ms = DEFAULT,218        @MaxWaitTimeForLock_s = DEFAULT,219        @ProcIDForJournal = DEFAULT,220        @HandleErrorSilent = 0,221        @ExecuteWithTransact = 0222      SELECT223        @FunctionDef = replace(REPLACE(@FunctionWhoPattern, '@CustomerCode', dbo.QBM_FCVStringToIndent(@SQLQuery,224        3)),225        '@FunctionWhoName',226        @FunctionWhoName)227      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @FunctionDef,228        @LockTimeout_ms = DEFAULT,229        @MaxWaitTimeForLock_s = DEFAULT,230        @ProcIDForJournal = DEFAULT,231        @HandleErrorSilent = 0,232        @ExecuteWithTransact = 0233      SELECT234        @FunctionDef = replace(REPLACE(@FunctionWhatPattern, '@CustomerCode', dbo.QBM_FCVStringToIndent(@SQLQuery,235        3)),236        '@FunctionWhatName',237        @FunctionWhatName)238      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @FunctionDef,239        @LockTimeout_ms = DEFAULT,240        @MaxWaitTimeForLock_s = DEFAULT,241        @ProcIDForJournal = DEFAULT,242        @HandleErrorSilent = 0,243        @ExecuteWithTransact = 0244      SELECT245        @cmdWho = CONCAT(@cmdWho,246        replace(replace(@SubBodyWho, '@TableName@', @TableName), '@FunctionWhoName', @FunctionWhoName))247      SELECT248        @cmdWhy = CONCAT(@cmdWhy,249        replace(replace(@SubBodyWhy, '@TableName@', @TableName), '@FunctionWhyName', @FunctionWhyName))250      SELECT251        @cmdWhat = CONCAT(@cmdWhat,252        replace(replace(@SubBodyWhat, '@TableName@', @TableName), '@FunctionWhatName', @FunctionWhatName))253      SELECT @ElementIndex += 1254    END255    SELECT256      @cmdWho = CONCAT(@HeaderWho,257      @cmdWho,258      @FooterWho)259    SELECT260      @cmdWhy = CONCAT(@HeaderWhy,261      @cmdWhy,262      @FooterWhy)263    SELECT264      @cmdWhat = CONCAT(@HeaderWhat,265      @Declares,266      @cmdWhat,267      @FooterWhat)268    IF @DebugSwitch > 0269    BEGIN270      EXEC sp_helptext @FunctionNameWho271      EXEC sp_helptext @FunctionNameWhy272      EXEC sp_helptext @FunctionNameWhat273    END274    BEGIN TRY275      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmdWho,276        @LockTimeout_ms = DEFAULT,277        @MaxWaitTimeForLock_s = DEFAULT,278        @ProcIDForJournal = DEFAULT,279        @HandleErrorSilent = 0,280        @ExecuteWithTransact = 0281      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmdWhy,282        @LockTimeout_ms = DEFAULT,283        @MaxWaitTimeForLock_s = DEFAULT,284        @ProcIDForJournal = DEFAULT,285        @HandleErrorSilent = 0,286        @ExecuteWithTransact = 0287      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmdWhat,288        @LockTimeout_ms = DEFAULT,289        @MaxWaitTimeForLock_s = DEFAULT,290        @ProcIDForJournal = DEFAULT,291        @HandleErrorSilent = 0,292        @ExecuteWithTransact = 0293    END TRY294    BEGIN CATCH295      EXEC QBM_PSessionErrorAdd DEFAULT296      SELECT @ResetMessage = ERROR_MESSAGE()297      IF @@TRANCOUNT > 0298      BEGIN299        ROLLBACK300      END301      EXEC QBM_PDBQueueCurrentResetNGen @SlotNumber,302        @ResetMessage,303        @@PROCID304    END CATCH305  END TRY306  BEGIN CATCH307    EXEC QBM_PSessionErrorAdd DEFAULT308    RAISERROR('',309    18,310    1)311      WITH NOWAIT312  END CATCH313  ende:314  RETURN315END
Open raw exported source
SQL ยท Raw172 lines
1   create   procedure QER_ZEntitlementSourceMakeProc (@SlotNumber int , @dummy1 varchar(38) , @Dummy2 varchar(38) , @GenProcIDDummy varchar(38) 2) as begin declare @DebugSwitch int = 0 declare @FunctionNameWho nvarchar(64) = 'QER_FTEntitlementSourceWho' declare @FunctionNameWhy nvarchar(64) = 'QER_FTEntitlementSourceWhy'3 declare @FunctionNameWhat nvarchar(64) = 'QER_FTEntitlementSourceWhat' declare @HeaderWho nvarchar(max) = concat('create or alter function dbo.' , @FunctionNameWho4 , '(5											@ObjectKeyToInspect varchar(138)6											)7	returns @erg table (8						UID_Person varchar(38) collate database_default9						)10 as11begin12'13 ) declare @SubBodyWho nvarchar(max) = '14	if @ObjectKeyToInspect like ''<Key><T>@TableName@</T>%''15	 begin16	  insert into @erg(uid_person)17	  select /*distinct */ xx.UID_Person18	   from dbo.@FunctionWhoName (@ObjectKeyToInspect) as xx1920	   goto EndLabel2122	 end -- @TableName@23'24 declare @FooterWho nvarchar(max) = '25	--insert into @erg(uid_person)26	--select distinct UID_Person27	-- from @work28 endLabel:2930 return31end32' declare33 @FunctionWhoPattern nvarchar(max) = '34create or alter function dbo.@FunctionWhoName (@ObjectKeyToInspect varchar(138)35									)36returns table37 38as return(39	  select distinct xx.UID_Person40	   from (41@CustomerCode42			) as xx43	   where xx.ObjectKeyToInspect = @ObjectKeyToInspect44)45'46 declare @FunctionWhoName varchar(30)  declare @HeaderWhy nvarchar(max) = concat('create or alter function dbo.' , @FunctionNameWhy , '(47											@UID_Person varchar(38) 48											, @ObjectKeyToInspect varchar(138)49											)50	returns @erg table (51						ObjectKeyReason1 varchar(138) collate database_default52						, ObjectKeyReason2 varchar(138) collate database_default53						)54 as55begin56'57 ) declare @SubBodyWhy nvarchar(max) = '58	if @ObjectKeyToInspect like ''<Key><T>@TableName@</T>%''59	 begin60	  insert into @erg(ObjectKeyReason1, ObjectKeyReason2)61	  select distinct xx.ObjectKeyReason1, xx.ObjectKeyReason262	   from dbo.@FunctionWhyName (@uid_person, @ObjectKeyToInspect) as xx6364	   goto EndLabel6566	 end -- @TableName@67'68 declare @FooterWhy nvarchar(max) = '69	--insert into @erg(ObjectKeyReason1, ObjectKeyReason2)70	--select distinct ObjectKeyReason1, ObjectKeyReason271	-- from @work7273	EndLabel:7475 return76end77'78 declare @FunctionWhyPattern nvarchar(max) = '79create or alter function dbo.@FunctionWhyName (@UID_Person varchar(38)80								, @ObjectKeyToInspect varchar(138)81									)82returns table83 84as return(85        select xx.ObjectKeyReason1, xx.ObjectKeyReason286		from (                                                                                    87@CustomerCode			88					) as xx89        where (xx.UID_Person = @UID_Person90                  or (xx.UID_Person is null 91                      )92               )93         and xx.ObjectKeyToInspect = @ObjectKeyToInspect94)95'96 declare @FunctionWhyName varchar(30)  declare @HeaderWhat nvarchar(max) = concat('create or alter function dbo.' , @FunctionNameWhat , '(97											@UID_Person varchar(38) 98											)99	returns @erg table (100						ObjectKeyToInspect varchar(138) collate database_default101						)102 as103begin104'105 ) declare @SubBodyWhat nvarchar(max) = '106	  insert into @work(ObjectKeyToInspect)107	  select xx.ObjectKeyToInspect108	   from dbo.@FunctionWhatName (@UID_Person) as xx109'110 declare @FooterWhat nvarchar(max) = '111	insert into @erg(ObjectKeyToInspect)112	select distinct ObjectKeyToInspect113	 from @work114 return115end116' declare117 @FunctionWhatPattern nvarchar(max) = '118create or alter function dbo.@FunctionWhatName (@UID_Person varchar(38)119									)120returns table121 122as return(123	  select xx.ObjectKeyToInspect124	   from (125@CustomerCode126			) as xx127	   where xx.UID_Person = @UID_Person128)129'130 declare @FunctionWhatName varchar(30)  declare @Declares nvarchar(max) = '131declare @work QER_YEntitlementSource132' declare @KlammerAuf nvarchar(max) 133= '134select 135	xx.UID_Person as UID_Person136	, xx.ObjectKeyToInspect as ObjectKeyToInspect137	, xx.ObjectKeyReason1 as ObjectKeyReason1138	, xx.ObjectKeyReason2 as ObjectKeyReason2139	from (140'141 declare @KlammerZu nvarchar(max) = '142	) as xx143' declare @cmdWho nvarchar(max) = '' declare @cmdWhy nvarchar(max) = '' declare @cmdWhat nvarchar(max)144 = '' declare @SQLQuery nvarchar(max) declare @Ident_QEREntitlementSource nvarchar(64) declare @TableName varchar(30) declare @ElementBuffer QBM_YCursorBuffer145 declare @ElementCount int declare @ElementIndex int declare @FunctionDef nvarchar(max) declare @ResetMessage nvarchar(4000) BEGIN TRY insert into @ElementBuffer146 (ContentFull, ContentShort, Ident1, UID1) select STRING_AGG( concat(@KlammerAuf , dbo.QBM_FCVStringToIndent(s.SQLQuery, 1) , @KlammerZu )  , '147	union all 148	'149 ) as SQLQuery , max(s.Ident_QEREntitlementSource) as Ident_QEREntitlementSource , t.TableName , max(s.UID_QEREntitlementSource) as UID_QEREntitlementSource150 from QEREntitlementSource s join DialogTable t with (readpast) on t.UID_DialogTable = s.UID_DialogTable group by t.TableName select @ElementCount = @@ROWCOUNT151 select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLQuery = bu.ContentFull , @Ident_QEREntitlementSource = bu.ContentShort152 , @TableName = bu.Ident1 , @FunctionWhyName = dbo.QBM_FGICodeName('FTEY', bu.UID1 ) , @FunctionWhoName = dbo.QBM_FGICodeName('FTEO', bu.UID1 ) , @FunctionWhatName153 = dbo.QBM_FGICodeName('FTEA', bu.UID1 ) from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @FunctionDef = replace(REPLACE(@FunctionWhyPattern154 , '@CustomerCode' , dbo.QBM_FCVStringToIndent(@SQLQuery, 3) ) , '@FunctionWhyName', @FunctionWhyName)   exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement155 = @FunctionDef , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 , @ExecuteWithTransact156 = 0 select @FunctionDef = replace(REPLACE(@FunctionWhoPattern , '@CustomerCode' , dbo.QBM_FCVStringToIndent(@SQLQuery, 3) ) , '@FunctionWhoName', @FunctionWhoName157)   exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @FunctionDef , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = 158default , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @FunctionDef = replace(REPLACE(@FunctionWhatPattern , '@CustomerCode' , dbo.QBM_FCVStringToIndent159(@SQLQuery, 3) ) , '@FunctionWhatName', @FunctionWhatName)   exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @FunctionDef , @LockTimeout_ms = default160 , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @cmdWho = concat(@cmdWho , 161replace(replace(@SubBodyWho, '@TableName@', @TableName) , '@FunctionWhoName', @FunctionWhoName) ) select @cmdWhy = concat(@cmdWhy , replace(replace(@SubBodyWhy162, '@TableName@', @TableName) , '@FunctionWhyName', @FunctionWhyName) ) select @cmdWhat = concat(@cmdWhat , replace(replace(@SubBodyWhat, '@TableName@',163 @TableName) , '@FunctionWhatName', @FunctionWhatName) ) select @ElementIndex += 1 end  select @cmdWho = concat(@HeaderWho ,  @cmdWho , @FooterWho) select164 @cmdWhy = concat(@HeaderWhy ,  @cmdWhy , @FooterWhy) select @cmdWhat = concat(@HeaderWhat , @Declares , @cmdWhat , @FooterWhat)  if @DebugSwitch > 0 begin165 exec sp_helptext @FunctionNameWho exec sp_helptext @FunctionNameWhy exec sp_helptext @FunctionNameWhat end BEGIN TRY   exec QBM_PExecuteSQLWithRetry_LLP166 @SQLStatement = @cmdWho , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 , @ExecuteWithTransact167 = 0   exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmdWhy , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default168 , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0   exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmdWhat , @LockTimeout_ms = default , @MaxWaitTimeForLock_s169 = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default select170 @ResetMessage = ERROR_MESSAGE() if @@TRANCOUNT > 0 begin rollback end  exec QBM_PDBQueueCurrentResetNGen @SlotNumber , @ResetMessage , @@PROCID END CATCH171 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 172