Back to OIM Explorer

dbo.QBM_PDashBoardDefine

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.130 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_FGIDashBoardCodeName source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FSQProcedureDef source text reference
  • references source dbo.QBM_PProcedureDrop source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSQLCreate source text reference
  • references source dbo.QBM_PViewDrop source text reference

References

Referenced By

Complete Source

SQL260 lines
1CREATE PROCEDURE QBM_PDashBoardDefine(2  @UID_DialogDashboardDef varchar(38),3  @GenProcIDDummy varchar(38),4  @IsToDrop BIT5)6  WITH7EXECUTE8AS9'dbo' AS10BEGIN11  DECLARE @QueryDefinition nvarchar(max)12  DECLARE @QueryDefinition100 nvarchar(max)13  DECLARE @viewname nvarchar(64)14  DECLARE @viewname100 nvarchar(64)15  DECLARE @procname nvarchar(64)16  DECLARE @SQLCmd nvarchar(max)17  DECLARE @ProcedureBody nvarchar(max)18  DECLARE @isInactive BIT19  DECLARE @IsDeactivatedByPreprocessor BIT20  DECLARE @IsManual BIT21  DECLARE @mitElementObjectKey BIT = 022  DECLARE @mitElementOrder BIT = 0declare @mitElementValueZ BIT = 023  DECLARE @mitElementObjectKey2 BIT = 024  DECLARE @ErrorMessage nvarchar(4000)25  DECLARE @ErrorSeverity int26  DECLARE @ErrorState int27  DECLARE @DebugSwitch int = 028  DECLARE @DebugLevel char(1) = 'W'29  SET XACT_ABORT OFF30  IF @IsToDrop = 131  BEGIN32    BEGIN33      TRANSACTION save TRANSACTION DashboardTry34    END35    BEGIN TRY36      SELECT37        TOP 1 @QueryDefinition = QueryDefinition,38        @QueryDefinition100 = QueryDefinition100,39        @isInactive = isInactive,40        @IsDeactivatedByPreprocessor = IsDeactivatedByPreprocessor,41        @IsManual = IsManual42      FROM dialogDashBoardDef43      WHERE44        UID_DialogDashBoardDef = @UID_DialogDashBoardDef45      SELECT46        @viewname = dbo.QBM_FGIDashBoardCodeName('view',47        @UID_DialogDashBoardDef)48      SELECT49        @viewname100 = dbo.QBM_FGIDashBoardCodeName('view100',50        @UID_DialogDashBoardDef)51      SELECT52        @procname = dbo.QBM_FGIDashBoardCodeName('proc',53        @UID_DialogDashBoardDef)54      IF NOT EXISTS(55        SELECT TOP 1 156        FROM DialogDashBoardDef d57        WHERE58          d.UID_DialogDashBoardDef = @UID_DialogDashboardDef)59      BEGIN60        GOTO DropOnly61      END62      IF(@isInactive = 1 AND @IsToDrop = 0) OR(@IsDeactivatedByPreprocessor = 1 AND @IsToDrop = 0) OR(@IsManual = 1)63      BEGIN64        GOTO ende65      END66      IF @QueryDefinition > ' '67      BEGIN68        SELECT69          @SQLCmd = N 'create or alter view dbo.' + @viewname + N ' as ' + @QueryDefinition70        IF @DebugSwitch > 071        BEGIN72          print @sqlcmd73        END74        EXEC QBM_PSQLCreate @viewname,75        'V',76          @SQLCmd,77          @UnComment = 078      END79      ELSE80      BEGIN81        GOTO ende82      END83      IF EXISTS(84        SELECT TOP 1 185        FROM QBM_VSchemaColumns86        WHERE87          table_name = @viewname AND Column_name = 'ElementObjectKey')88      BEGIN89        SELECT @mitElementObjectKey = 190      END91      IF EXISTS(92        SELECT TOP 1 193        FROM QBM_VSchemaColumns94        WHERE95          table_name = @viewname AND Column_name = 'ElementOrder')96      BEGIN97        SELECT @mitElementOrder = 198      END99      IF NOT EXISTS(100        SELECT TOP 1 1101        FROM INFORMATION_SCHEMA.COLUMNS c102        WHERE103          c.TABLE_NAME = @viewname AND Column_name = 'ElementValue' AND c.DATA_TYPE IN('int', 'float'))104      BEGIN105        SELECT106          @ErrorMessage = '#LDS#Invalid data type for field {0}, column {1}.|QueryDefinition|ElementValue|'107        RAISERROR(@ErrorMessage,108        18,109        2)110          WITH nowait111      END112      IF EXISTS(113        SELECT TOP 1 1114        FROM QBM_VSchemaColumns115        WHERE116          table_name = @viewname AND Column_name = 'ElementValueZ')117      BEGIN118        SELECT @mitElementValueZ = 1119        IF NOT EXISTS(120          SELECT TOP 1 1121          FROM INFORMATION_SCHEMA.COLUMNS c122          WHERE123            c.TABLE_NAME = @viewname AND Column_name = 'ElementValueZ' AND c.DATA_TYPE IN('int', 'float'))124        BEGIN125          SELECT126            @ErrorMessage = '#LDS#Invalid data type for field {0}, column {1}.|QueryDefinition|ElementValueZ|'127          RAISERROR(@ErrorMessage,128          18,129          2)130            WITH nowait131        END132      END133      IF EXISTS(134        SELECT TOP 1 1135        FROM QBM_VSchemaColumns136        WHERE137          table_name = @viewname AND Column_name = 'ElementObjectKey2')138      BEGIN139        SELECT @mitElementObjectKey2 = 1140      END141      IF @QueryDefinition100 > ' '142      BEGIN143        SELECT144          @SQLCmd = N 'create or alter view dbo.' + @viewname100 + N ' as ' + @QueryDefinition100145      END146      ELSE147      BEGIN148        SELECT149          @SQLCmd = N 'create or alter view dbo.' + @viewname100 + N ' as ' + N 'select convert(nvarchar(255), null) as ElementName, convert(float, 0) as ElementValue'150        IF @mitElementObjectKey = 1151        BEGIN152          SELECT153            @SQLCmd = @SQLCmd + N ', convert(varchar(138),null) as ElementObjectKey'154        END155        SELECT @SQLCmd = @SQLCmd + '156		where 1= 0'157      END158      IF @DebugSwitch > 0159      BEGIN160        print @sqlcmd161      END162      EXEC QBM_PSQLCreate @viewname100,163      'V',164        @SQLCmd,165        @UnComment = 0166      IF NOT EXISTS(167        SELECT TOP 1 1168        FROM INFORMATION_SCHEMA.COLUMNS c169        WHERE170          c.TABLE_NAME = @viewname100 AND Column_name = 'ElementValue' AND c.DATA_TYPE IN('int', 'float'))171      BEGIN172        SELECT173          @ErrorMessage = '#LDS#Invalid data type for field {0}, column {1}.|QueryDefinition100|ElementValue|'174        RAISERROR(@ErrorMessage,175        18,176        2)177          WITH nowait178      END179      SELECT @SQLCmd = N 'create or alter procedure dbo.' + @procname + N ' as '180      SELECT181        @ProcedureBody = '182		insert into #DialogDashBoardContent (  ElementName, ElementValue, ElementValue100, ElementObjectKey183											, ElementValueZ, ElementObjectKey2 )184			select left(e.ElementName, 255), e.ElementValue, e100.ElementValue, '185        + CASE @mitElementObjectKey186        WHEN 1 THEN187        N ' e.ElementObjectKey'188      ELSE N ' NULL as ElementObjectKey'189      END + '190										,  ' + CASE @mitElementValueZ191      WHEN 1 THEN192      N ' e.ElementValueZ'193      ELSE N ' NULL as ElementValueZ'194      END + '195										, ' + CASE @mitElementObjectKey2196      WHEN 1 THEN197      N ' e.ElementObjectKey2'198      ELSE N ' NULL as ElementObjectKey2'199      END + '200				from ' + @viewname + N ' e 201							left outer join ' + @viewname100 + N ' e100 on e.ElementName = e100.ElementName'202      IF @mitElementObjectKey = 1203      BEGIN204        SELECT205          @ProcedureBody = @ProcedureBody + N '206														and e.ElementObjectKey = e100.ElementObjectKey'207      END208      IF @MitElementOrder = 1209      BEGIN210        SELECT211          @ProcedureBody = @ProcedureBody + N '212					order by ElementOrder, e.ElementName'213      END214      ELSE215      BEGIN216        SELECT @ProcedureBody = @ProcedureBody + N '217				order by e.ElementName'218      END219      IF @mitElementObjectKey = 1220      BEGIN221        SELECT222          @ProcedureBody = @ProcedureBody + N ', e.ElementObjectKey'223      END224      SELECT225        @SQLCmd = dbo.QBM_FSQProcedureDef(@procname,226        '',227        @ProcedureBody,228        'DashBoard definition: ' + @UID_DialogDashboardDef)229      IF @DebugSwitch > 0230      BEGIN231        print @SQLCmd232      END233      EXEC QBM_PSQLCreate @procname,234      'P',235        @SQLCmd,236        @UnComment = 0237    END TRY238    BEGIN CATCH239      EXEC QBM_PSessionErrorAdd DEFAULT240      IF @@TRANCOUNT > 0241      BEGIN242        ROLLBACK TRANSACTION;243      END244      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()245      RAISERROR(@Rethrow,246      18,247      1)248        WITH NOWAIT249    END CATCH250    ende:251    IF @IsToDrop = 1252    BEGIN253      ROLLBACK TRANSACTION DashboardTry COMMIT TRANSACTION254    END255    RETURN DropOnly:256    EXEC QBM_PProcedureDrop @procname257    EXEC QBM_PViewDrop @viewname258    EXEC QBM_PViewDrop @viewname100259    RETURN260  END
Open raw exported source
SQL ยท Raw49 lines
1    create   procedure QBM_PDashBoardDefine ( @UID_DialogDashboardDef varchar(38) , @GenProcIDDummy varchar(38) , @IsToDrop bit ) with execute as2 'dbo' as begin declare @QueryDefinition nvarchar(max) declare @QueryDefinition100 nvarchar(max) declare @viewname nvarchar(64) declare @viewname100 nvarchar3(64) declare @procname nvarchar(64) declare @SQLCmd nvarchar(max) declare @ProcedureBody nvarchar(max) declare @isInactive bit declare @IsDeactivatedByPreprocessor4 bit declare @IsManual bit declare @mitElementObjectKey bit = 0 declare @mitElementOrder bit = 0declare @mitElementValueZ bit = 0 declare @mitElementObjectKey25 bit = 0  declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int declare @DebugSwitch int = 0 declare @DebugLevel char6(1) = 'W' SET XACT_ABORT OFF if @IsToDrop = 1 begin begin transaction save transaction DashboardTry end BEGIN TRY select top 1 @QueryDefinition = QueryDefinition7, @QueryDefinition100 = QueryDefinition100 , @isInactive = isInactive , @IsDeactivatedByPreprocessor = IsDeactivatedByPreprocessor , @IsManual = IsManual8 from dialogDashBoardDef where UID_DialogDashBoardDef = @UID_DialogDashBoardDef select @viewname = dbo.QBM_FGIDashBoardCodeName( 'view', @UID_DialogDashBoardDef9)  select @viewname100 = dbo.QBM_FGIDashBoardCodeName( 'view100', @UID_DialogDashBoardDef)  select @procname = dbo.QBM_FGIDashBoardCodeName( 'proc', @UID_DialogDashBoardDef10)  if not exists (select top 1 1 from DialogDashBoardDef d where d.UID_DialogDashBoardDef = @UID_DialogDashboardDef ) begin goto DropOnly end if (@isInactive11 = 1 and @IsToDrop = 0) or (@IsDeactivatedByPreprocessor = 1 and @IsToDrop = 0) or (@IsManual = 1) begin goto ende end if @QueryDefinition > ' ' begin 12select @SQLCmd = N'create or alter view dbo.' + @viewname + N' as ' + @QueryDefinition if @DebugSwitch > 0 begin print @sqlcmd end exec QBM_PSQLCreate 13@viewname, 'V', @SQLCmd, @UnComment = 0 end else begin  goto ende end if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @viewname and14 Column_name = 'ElementObjectKey' ) begin select @mitElementObjectKey = 1 end if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @viewname15 and Column_name = 'ElementOrder' ) begin select @mitElementOrder = 1 end if not exists (select top 1 1  from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME16 = @viewname and Column_name = 'ElementValue' and c.DATA_TYPE in ('int', 'float') ) begin select @ErrorMessage = '#LDS#Invalid data type for field {0}, column {1}.|QueryDefinition|ElementValue|'17 raiserror (@ErrorMessage, 18, 2) with nowait end if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @viewname and Column_name = 'ElementValueZ'18 ) begin select @mitElementValueZ = 1  if not exists (select top 1 1  from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME = @viewname and Column_name 19= 'ElementValueZ' and c.DATA_TYPE in ('int', 'float') ) begin select @ErrorMessage = '#LDS#Invalid data type for field {0}, column {1}.|QueryDefinition|ElementValueZ|'20 raiserror (@ErrorMessage, 18, 2) with nowait end end if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @viewname and Column_name = 21'ElementObjectKey2' ) begin select @mitElementObjectKey2 = 1 end  if @QueryDefinition100 > ' ' begin select @SQLCmd = N'create or alter view dbo.' + @viewname10022 + N' as ' + @QueryDefinition100 end else begin  select @SQLCmd = N'create or alter view dbo.' + @viewname100 + N' as ' + N'select convert(nvarchar(255), null) as ElementName, convert(float, 0) as ElementValue'23  if @mitElementObjectKey = 1 begin select @SQLCmd = @SQLCmd + N', convert(varchar(138),null) as ElementObjectKey' end select @SQLCmd = @SQLCmd + '24		where 1= 0'25 end if @DebugSwitch > 0 begin print @sqlcmd end exec QBM_PSQLCreate @viewname100, 'V', @SQLCmd, @UnComment = 0 if not exists (select top 1 1  from INFORMATION_SCHEMA.COLUMNS26 c where c.TABLE_NAME = @viewname100 and Column_name = 'ElementValue' and c.DATA_TYPE in ('int', 'float') ) begin select @ErrorMessage = '#LDS#Invalid data type for field {0}, column {1}.|QueryDefinition100|ElementValue|'27 raiserror (@ErrorMessage, 18, 2) with nowait end   select @SQLCmd = N'create or alter procedure dbo.' + @procname + N' as ' select @ProcedureBody = '28		insert into #DialogDashBoardContent (  ElementName, ElementValue, ElementValue100, ElementObjectKey29											, ElementValueZ, ElementObjectKey2 )30			select left(e.ElementName, 255), e.ElementValue, e100.ElementValue, '31 + case @mitElementObjectKey when 1 then N' e.ElementObjectKey' else N' NULL as ElementObjectKey' end + '32										,  ' + case @mitElementValueZ when33 1 then N' e.ElementValueZ' else N' NULL as ElementValueZ' end + '34										, ' + case @mitElementObjectKey2 when 1 then N' e.ElementObjectKey2' else35 N' NULL as ElementObjectKey2' end + '36				from ' + @viewname + N' e 37							left outer join ' + @viewname100 + N' e100 on e.ElementName = e100.ElementName'38  if @mitElementObjectKey = 1 begin select @ProcedureBody = @ProcedureBody + N'39														and e.ElementObjectKey = e100.ElementObjectKey' end if @MitElementOrder40 = 1 begin select @ProcedureBody = @ProcedureBody + N'41					order by ElementOrder, e.ElementName' end else begin  select @ProcedureBody = @ProcedureBody42 + N'43				order by e.ElementName' end if @mitElementObjectKey = 1 begin select @ProcedureBody = @ProcedureBody + N', e.ElementObjectKey' end select @SQLCmd44 = dbo.QBM_FSQProcedureDef(@procname, '', @ProcedureBody , 'DashBoard definition: ' + @UID_DialogDashboardDef ) if @DebugSwitch > 0 begin print @SQLCmd45 end exec QBM_PSQLCreate @procname, 'P', @SQLCmd, @UnComment = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if @@TRANCOUNT > 0 BEGIN ROLLBACK46 TRANSACTION; END declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende:  if @IsToDrop47 = 1 begin rollback transaction DashboardTry commit transaction end return DropOnly: exec QBM_PProcedureDrop @procname exec QBM_PViewDrop @viewname exec48 QBM_PViewDrop @viewname100 return end 49