dbo.QBM_PDashBoardDefine

1    create   procedure QBM_PDashBoardDefine ( @UID_DialogDashboardDef varchar(38) , @GenProcIDDummy varchar(38) , @IsToDrop bit ) with execute as
2 'dbo' as begin declare @QueryDefinition nvarchar(max) declare @QueryDefinition100 nvarchar(max) declare @viewname nvarchar(64) declare @viewname100 nvarchar
3(64) declare @procname nvarchar(64) declare @SQLCmd nvarchar(max) declare @ProcedureBody nvarchar(max) declare @isInactive bit declare @IsDeactivatedByPreprocessor
4 bit declare @IsManual bit declare @mitElementObjectKey bit = 0 declare @mitElementOrder bit = 0declare @mitElementValueZ bit = 0 declare @mitElementObjectKey2
5 bit = 0  declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int declare @DebugSwitch int = 0 declare @DebugLevel char
6(1) = 'W' SET XACT_ABORT OFF if @IsToDrop = 1 begin begin transaction save transaction DashboardTry end BEGIN TRY select top 1 @QueryDefinition = QueryDefinition
7, @QueryDefinition100 = QueryDefinition100 , @isInactive = isInactive , @IsDeactivatedByPreprocessor = IsDeactivatedByPreprocessor , @IsManual = IsManual
8 from dialogDashBoardDef where UID_DialogDashBoardDef = @UID_DialogDashBoardDef select @viewname = dbo.QBM_FGIDashBoardCodeName( 'view', @UID_DialogDashBoardDef
9)  select @viewname100 = dbo.QBM_FGIDashBoardCodeName( 'view100', @UID_DialogDashBoardDef)  select @procname = dbo.QBM_FGIDashBoardCodeName( 'proc', @UID_DialogDashBoardDef
10)  if not exists (select top 1 1 from DialogDashBoardDef d where d.UID_DialogDashBoardDef = @UID_DialogDashboardDef ) begin goto DropOnly end if (@isInactive
11 = 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 and
14 Column_name = 'ElementObjectKey' ) begin select @mitElementObjectKey = 1 end if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @viewname
15 and Column_name = 'ElementOrder' ) begin select @mitElementOrder = 1 end if not exists (select top 1 1  from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME
16 = @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.' + @viewname100
22 + 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.COLUMNS
26 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, ElementObjectKey
29											, 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 when
33 1 then N' e.ElementValueZ' else N' NULL as ElementValueZ' end + '
34										, ' + case @mitElementObjectKey2 when 1 then N' e.ElementObjectKey2' else
35 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 @MitElementOrder
40 = 1 begin select @ProcedureBody = @ProcedureBody + N'
41					order by ElementOrder, e.ElementName' end else begin  select @ProcedureBody = @ProcedureBody
42 + N'
43				order by e.ElementName' end if @mitElementObjectKey = 1 begin select @ProcedureBody = @ProcedureBody + N', e.ElementObjectKey' end select @SQLCmd
44 = dbo.QBM_FSQProcedureDef(@procname, '', @ProcedureBody , 'DashBoard definition: ' + @UID_DialogDashboardDef ) if @DebugSwitch > 0 begin print @SQLCmd
45 end exec QBM_PSQLCreate @procname, 'P', @SQLCmd, @UnComment = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if @@TRANCOUNT > 0 BEGIN ROLLBACK
46 TRANSACTION; END declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende:  if @IsToDrop
47 = 1 begin rollback transaction DashboardTry commit transaction end return DropOnly: exec QBM_PProcedureDrop @procname exec QBM_PViewDrop @viewname exec
48 QBM_PViewDrop @viewname100 return end 
49