property.usp_RepoObject_Inheritance - P

type: P ( stored procedure ), modify_date: 2021-11-17 19:24:15

RepoObject_guid: 63B33A4A-426D-EB11-84E2-A81E8446D5B0

Description

Examples

Parameters

  • @execution_instance_guid (uniqueidentifier)

  • @ssis_execution_id (bigint)

  • @sub_execution_id (int)

  • @parent_execution_log_id (bigint)

Entity Diagram

entity-property.usp_repoobject_inheritance

References

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-property.usp_repoobject_inheritance

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-property.usp_repoobject_inheritance

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-property.usp_repoobject_inheritance

sql_modules_definition

property.usp_RepoObject_Inheritance - P script
CREATE Procedure property.usp_RepoObject_Inheritance
    ----keep the code between logging parameters and "START" unchanged!
    ---- parameters, used for logging; you don't need to care about them, but you can use them, wenn calling from SSIS or in your workflow to log the context of the procedure call
    @execution_instance_guid UniqueIdentifier = Null --SSIS system variable ExecutionInstanceGUID could be used, any other unique guid is also fine. If NULL, then NEWID() is used to create one
  , @ssis_execution_id       BigInt           = Null --only SSIS system variable ServerExecutionID should be used, or any other consistent number system, do not mix different number systems
  , @sub_execution_id        Int              = Null --in case you log some sub_executions, for example in SSIS loops or sub packages
  , @parent_execution_log_id BigInt           = Null --in case a sup procedure is called, the @current_execution_log_id of the parent procedure should be propagated here. It allowes call stack analyzing
As
Declare
    --
    @current_execution_log_id BigInt                                           --this variable should be filled only once per procedure call, it contains the first logging call for the step 'start'.
  , @current_execution_guid   UniqueIdentifier
    = NewId ()                                                                 --a unique guid for any procedure call. It should be propagated to sub procedures using "@parent_execution_log_id = @current_execution_log_id"
  , @source_object            NVarchar(261)  = Null                            --use it like '[schema].[object]', this allows data flow vizualizatiuon (include square brackets)
  , @target_object            NVarchar(261)  = Null                            --use it like '[schema].[object]', this allows data flow vizualizatiuon (include square brackets)
  , @proc_id                  Int            = @@ProcId
  , @proc_schema_name         NVarchar(128)  = Object_Schema_Name ( @@ProcId ) --schema ande name of the current procedure should be automatically logged
  , @proc_name                NVarchar(128)  = Object_Name ( @@ProcId )        --schema ande name of the current procedure should be automatically logged
  , @event_info               NVarchar(Max)
  , @step_id                  Int            = 0
  , @step_name                NVarchar(1000) = Null
  , @rows                     Int
  , @loop_rows                Int
  , @loop_count               Int

--[event_info] get's only the information about the "outer" calling process
--wenn the procedure calls sub procedures, the [event_info] will not change
Set @event_info =
(
    Select
        event_info
    From
        sys.dm_exec_input_buffer ( @@Spid, Current_Request_Id ())
);

If @execution_instance_guid Is Null
    Set @execution_instance_guid = NewId ();

--
--SET @rows = @@ROWCOUNT;
Set @step_id = @step_id + 1;
Set @step_name = N'start';
Set @source_object = Null;
Set @target_object = Null;

Exec logs.usp_ExecutionLog_insert
    --these parameters should be the same for all logging execution
    @execution_instance_guid = @execution_instance_guid
  , @ssis_execution_id = @ssis_execution_id
  , @sub_execution_id = @sub_execution_id
  , @parent_execution_log_id = @parent_execution_log_id
  , @current_execution_guid = @current_execution_guid
  , @proc_id = @proc_id
  , @proc_schema_name = @proc_schema_name
  , @proc_name = @proc_name
  , @event_info = @event_info
                            --the following parameters are individual for each call
  , @step_id = @step_id     --@step_id should be incremented before each call
  , @step_name = @step_name --assign individual step names for each call
                            --only the "start" step should return the log id into @current_execution_log_id
                            --all other calls should not overwrite @current_execution_log_id
  , @execution_log_id = @current_execution_log_id Output;

----you can log the content of your own parameters, do this only in the start-step
----data type is sql_variant
--

--keep the code between logging parameters and "START" unchanged!
--
----START
--
----- start here with your own code
Print '[property].[usp_RepoObject_Inheritance]'

Exec property.usp_RepoObjectProperty_collect

Exec property.usp_PERSIST_PropertyName_RepoObject_T

Set @loop_count = 1

While @loop_count = 1 Or @loop_rows > 0
Begin -- loop
    Set @loop_rows = 0

    Declare inheritance_cursor Cursor Local Fast_Forward For
    Select
        resulting_InheritanceDefinition
    From
        property.RepoObjectProperty_InheritanceType_resulting_InheritanceDefinition
    Group By
        resulting_InheritanceDefinition
    Having
        ( Not ( resulting_InheritanceDefinition Is Null ));

    Declare
        @resulting_InheritanceDefinition        NVarchar(4000)
      , @resulting_InheritanceDefinition_ForSql NVarchar(4000);
    Declare @stmt NVarchar(Max);

    Open inheritance_cursor;

    Fetch Next From inheritance_cursor
    Into
        @resulting_InheritanceDefinition;

    While ( @@Fetch_Status <> -1 )
    Begin
        If ( @@Fetch_Status <> -2 )
        Begin
            Print @resulting_InheritanceDefinition;

            --replace "'" by "''" to be used in a string
            Set @resulting_InheritanceDefinition_ForSql = Replace ( @resulting_InheritanceDefinition, '''', '''''' );

            --PRINT @resulting_InheritanceDefinition_ForSql
            Truncate Table property.RepoObject_Inheritance_temp;

            /*
INSERT INTO [property].[RepoObject_Inheritance_temp] (
 [RepoObject_guid]
 , [property_name]
 , [property_value]
 , [property_value_new]
 , [InheritanceType]
 , [Inheritance_StringAggSeparatorSql]
 , [is_force_inherit_empty_source]
 , [is_StringAggAllSources]
 , [resulting_InheritanceDefinition]
 , [RowNumberSource]
 , [referenced_RepoObject_guid]
 , [referenced_RepoObject_fullname]
 , [referenced_RepoObject_name]
 , [referencing_RepoObject_fullname]
 , [referencing_RepoObject_name]
 )
SELECT
 --
 [T1].[RepoObject_guid]
 , [T1].[property_name]
 , [T1].[property_value]
 , [property_value_new] = COALESCE([referencing].[Repo_definition], [property].[fs_get_RepoObjectProperty_nvarchar]([referenced].[RepoObject_guid], 'MS_Description'))
 , [T1].[InheritanceType]
 , [T1].[Inheritance_StringAggSeparatorSql]
 , [T1].[is_force_inherit_empty_source]
 , [T1].[is_StringAggAllSources]
 , [T1].[resulting_InheritanceDefinition]
 , [RowNumberSource] = ROW_NUMBER() OVER (
  PARTITION BY [T1].[RepoObject_guid] ORDER BY
     Iif(Inheritance_Source_fullname = referenced.RepoObject_fullname, 1, 2)
   , [referenced].[RepoObject_fullname]
   , [referenced].[RepoObject_name]
  )
 , [T2].[referenced_RepoObject_guid]
 , [referenced_RepoObject_fullname] = [referenced].[RepoObject_fullname]
 , [referenced_RepoObject_name] = [referenced].[RepoObject_name]
 , [referencing_RepoObject_fullname] = [referencing].[RepoObject_fullname]
 , [referencing_RepoObject_name] = [referencing].[RepoObject_name]
FROM [property].[RepoObjectProperty_InheritanceType_resulting_InheritanceDefinition] AS T1
INNER JOIN [reference].[RepoObject_reference_union] AS T2
 ON T2.[referencing_RepoObject_guid] = T1.[RepoObject_guid]
INNER JOIN [repo].[RepoObject_gross] AS referencing
 ON referencing.[RepoObject_guid] = T1.[RepoObject_guid]
INNER JOIN [repo].[RepoObject_gross] AS referenced
 ON referenced.[RepoObject_guid] = T2.[referenced_RepoObject_guid]
WHERE [T1].[resulting_InheritanceDefinition] = 'COALESCE(referencing.[Repo_definition], property.fs_get_RepoObjectProperty_nvarchar(referenced.[RepoObject_guid], ''MS_Description''))'

*/
            Set @stmt
                = N'
INSERT INTO [property].[RepoObject_Inheritance_temp] (
 [RepoObject_guid]
 , [property_name]
 , [property_value]
 , [property_value_new]
 , [InheritanceType]
 , [Inheritance_StringAggSeparatorSql]
 , [is_force_inherit_empty_source]
 , [is_StringAggAllSources]
 , [resulting_InheritanceDefinition]
 , [RowNumberSource]
 , [referenced_RepoObject_guid]
 , [referenced_RepoObject_fullname]
 , [referenced_RepoObject_name]
 , [referencing_RepoObject_fullname]
 , [referencing_RepoObject_name]
 )
SELECT
 --
 [T1].[RepoObject_guid]
 , [T1].[property_name]
 , [T1].[property_value]
 , [property_value_new] = ' + @resulting_InheritanceDefinition
                  + N'
 , [T1].[InheritanceType]
 , [T1].[Inheritance_StringAggSeparatorSql]
 , [T1].[is_force_inherit_empty_source]
 , [T1].[is_StringAggAllSources]
 , [T1].[resulting_InheritanceDefinition]
 , [RowNumberSource] = ROW_NUMBER() OVER (
  PARTITION BY [T1].[RepoObject_guid] ORDER BY
     Iif(Inheritance_Source_fullname = referenced.RepoObject_fullname, 1, 2)
   , [referenced].[RepoObject_fullname]
  )
 , [T2].[referenced_RepoObject_guid]
 , [referenced_RepoObject_fullname] = [referenced].[RepoObject_fullname]
 , [referenced_RepoObject_name] = [referenced].[RepoObject_name]
 , [referencing_RepoObject_fullname] = [referencing].[RepoObject_fullname]
 , [referencing_RepoObject_name] = [referencing].[RepoObject_name]
FROM [property].[RepoObjectProperty_InheritanceType_resulting_InheritanceDefinition] AS T1
INNER JOIN [reference].[RepoObject_reference_union] AS T2
 ON T2.[referencing_RepoObject_guid] = T1.[RepoObject_guid]
INNER JOIN [repo].[RepoObject_gross] AS referencing
 ON referencing.[RepoObject_guid] = T1.[RepoObject_guid]
INNER JOIN [repo].[RepoObject_gross] AS referenced
 ON referenced.[RepoObject_guid] = T2.[referenced_RepoObject_guid]
WHERE [T1].[resulting_InheritanceDefinition] = ''' + @resulting_InheritanceDefinition_ForSql + N'''
'           ;

            Print @stmt;
            Print GetDate ()

            Execute sys.sp_executesql @stmt = @stmt;

            Declare separator_cursor Cursor Read_Only For
            Select
                Inheritance_StringAggSeparatorSql
            From
                property.RepoObject_Inheritance_temp
            Group By
                Inheritance_StringAggSeparatorSql;

            Declare @Inheritance_StringAggSeparatorSql NVarchar(4000);

            Open separator_cursor;

            Fetch Next From separator_cursor
            Into
                @Inheritance_StringAggSeparatorSql;

            --Set @loop_count = 0
            --Set @rows = 0
            While ( @@Fetch_Status <> -1 )
            Begin
                If ( @@Fetch_Status <> -2 )
                Begin
                    Print @Inheritance_StringAggSeparatorSql
                    Print GetDate ()

                    If @Inheritance_StringAggSeparatorSql Is Null
                    Begin
                        --While @loop_count = 0 Or @rows > 0
                        --Begin --merge loop

                        --[is_StringAggAllSources] = 0
                        --T.[property_value] can't be NULL
                        --not [property_value_new] IS NULL
                        --we need to delete, when S.[property_value_new] IS NULL
                        Merge Into property.RepoObjectProperty As T
                        Using
                        (
                            Select
                                RepoObject_guid
                              , property_name
                              , property_value
                              , property_value_new
                            --, [InheritanceType]
                            --, [Inheritance_StringAggSeparatorSql]
                            --, [is_force_inherit_empty_source]
                            --, [is_StringAggAllSources]
                            --, [resulting_InheritanceDefinition]
                            --, [RowNumberSource]
                            --, [referenced_RepoObject_guid]
                            --, [referenced_RepoObject_fullname]
                            --, [referenced_RepoObject_name]
                            --, [referencing_RepoObject_fullname]
                            --, [referencing_RepoObject_name]
                            From
                                property.RepoObject_Inheritance_temp
                            Where
                                --
                                is_StringAggAllSources                                 = 0
                                --only the first source
                                And RowNumberSource                                    = 1
                                And
                                (
                                    is_force_inherit_empty_source                      = 1
                                    Or Not property_value_new Is Null
                                )
                                And
                                (
                                    property_value Is Null
                                    Or property_value Collate Latin1_General_100_CS_AS <> property_value_new Collate Latin1_General_100_CS_AS
                                    Or
                                    (
                                        Not property_value Is Null
                                        And is_force_inherit_empty_source              = 1
                                        And property_value_new Is Null
                                    )
                                )
                        ) As S
                        On S.RepoObject_guid = T.RepoObject_guid
                           And S.property_name = T.property_name
                        When Matched And (
                                             Not S.property_value_new Is Null
                                             And T.property_value Is Null
                                             Or T.property_value Collate Latin1_General_100_CS_AS <> S.property_value_new Collate Latin1_General_100_CS_AS
                                         )
                            Then Update Set
                                     T.property_value = S.property_value_new
                        When Matched And S.property_value_new Is Null
                            Then Delete
                        When Not Matched By Target And Not S.property_value_new Is Null
                            Then Insert
                                 (
                                     RepoObject_guid
                                   , property_name
                                   , property_value
                                 )
                                 Values
                                     (
                                         S.RepoObject_guid
                                       , S.property_name
                                       , S.property_value_new
                                     )
                        ----Output issue because of calculated column in target table
                        --Output
                        --    deleted.*
                        --  , $ACTION
                        --  , inserted.*
                        ;

                        Set @rows = @@RowCount
                        Set @loop_rows = @loop_rows + @rows

                        Print Concat ( '@rows: ', @rows, ';@loop_rows: ', @loop_rows )

                    --Set @loop_count = @loop_count + 1
                    --End --merge loop
                    End
                    Else
                    Begin
                        --While @loop_count = 0 Or @rows > 0
                        --Begin --merge loop
                        Merge Into property.RepoObjectProperty As T
                        Using
                        (
                            Select
                                T1.RepoObject_guid
                              , T1.property_name
                              , T1.property_value
                              , T1.property_value_new
                            --, [is_force_inherit_empty_source]
                            --, [RowNumberSource]
                            From
                        (
                            Select
                                RepoObject_guid
                              , property_name
                              , property_value                = Max ( property_value )
                              , property_value_new            = Cast(String_Agg (
                                                                                    Cast(property_value_new As NVarchar(Max))
                                                                                  , @Inheritance_StringAggSeparatorSql
                                                                                ) Within Group(Order By
                                                                                                   RowNumberSource) As NVarchar(4000))
                              --, [property_value_new] = CAST(STRING_AGG(CAST([property_value_new] as NVARCHAR(MAX)), CHAR(13)+CHAR(10)) WITHIN GROUP ( ORDER BY [RowNumberSource]) as NVARCHAR(4000))
                              --, [property_value_new] = CAST(STRING_AGG(CAST([property_value_new] as NVARCHAR(MAX)), ';') WITHIN GROUP ( ORDER BY [RowNumberSource]) as NVARCHAR(4000))
                              --, [Inheritance_StringAggSeparatorSql]
                              , is_force_inherit_empty_source = Max ( is_force_inherit_empty_source )
                              --, [is_StringAggAllSources]
                              --, [resulting_InheritanceDefinition]
                              , RowNumberSource               = Max ( RowNumberSource )
                            --, [referenced_RepoObject_guid]
                            --, [referenced_RepoObject_fullname]
                            --, [referenced_RepoObject_name]
                            --, [referencing_RepoObject_fullname]
                            --, [referencing_RepoObject_name]
                            From
                                property.RepoObject_Inheritance_temp
                            Where
                                --
                                is_StringAggAllSources = 1
                            Group By
                                RepoObject_guid
                              , property_name
                        ) As T1
                            Where
                                (
                                    T1.is_force_inherit_empty_source                      = 1
                                    Or Not T1.property_value_new Is Null
                                )
                                And
                                (
                                    T1.property_value Is Null
                                    Or T1.property_value Collate Latin1_General_100_CS_AS <> T1.property_value_new Collate Latin1_General_100_CS_AS
                                    Or
                                    (
                                        Not T1.property_value Is Null
                                        And T1.is_force_inherit_empty_source              = 1
                                        And T1.property_value_new Is Null
                                    )
                                )
                        ) As S
                        On S.RepoObject_guid = T.RepoObject_guid
                           And S.property_name = T.property_name
                        When Matched And (
                                             Not S.property_value_new Is Null
                                             And T.property_value Is Null
                                             Or T.property_value Collate Latin1_General_100_CS_AS <> S.property_value_new Collate Latin1_General_100_CS_AS
                                         )
                            Then Update Set
                                     T.property_value = S.property_value_new
                        When Matched And S.property_value_new Is Null
                            Then Delete
                        When Not Matched By Target And Not S.property_value_new Is Null
                            Then Insert
                                 (
                                     RepoObject_guid
                                   , property_name
                                   , property_value
                                 )
                                 Values
                                     (
                                         S.RepoObject_guid
                                       , S.property_name
                                       , S.property_value_new
                                     )
                        ----Output issue because of calculated column in target table
                        --Output
                        --    deleted.*
                        --  , $ACTION
                        --  , inserted.*
                        ;

                        Set @rows = @@RowCount
                        Set @loop_rows = @loop_rows + @rows

                        Print Concat ( '@rows: ', @rows, ';@loop_rows: ', @loop_rows )

                    --Set @loop_count = @loop_count + 1
                    End
                --End --merge loop
                End;

                Fetch Next From separator_cursor
                Into
                    @Inheritance_StringAggSeparatorSql;
            End;

            Close separator_cursor;
            Deallocate separator_cursor;
        End;

        -- Logging START --
        --Set @rows = @@RowCount
        Set @step_id = @step_id + 1
        Set @step_name = Concat ( @resulting_InheritanceDefinition, '; loop:', @loop_count )
        Set @source_object = N'[property].[RepoObjectProperty_InheritanceType_resulting_InheritanceDefinition]'
        Set @target_object = N'[property].[RepoObjectProperty]'

        Exec logs.usp_ExecutionLog_insert
            @execution_instance_guid = @execution_instance_guid
          , @ssis_execution_id = @ssis_execution_id
          , @sub_execution_id = @sub_execution_id
          , @parent_execution_log_id = @parent_execution_log_id
          , @current_execution_guid = @current_execution_guid
          , @proc_id = @proc_id
          , @proc_schema_name = @proc_schema_name
          , @proc_name = @proc_name
          , @event_info = @event_info
          , @step_id = @step_id
          , @step_name = @step_name
          , @source_object = @source_object
          , @target_object = @target_object
          , @updated = @rows
          , @info_01 = @loop_count
          , @info_02 = @loop_rows

        -- Logging END --
        Fetch Next From inheritance_cursor
        Into
            @resulting_InheritanceDefinition;
    End;

    Close inheritance_cursor;
    Deallocate inheritance_cursor;

    Set @loop_count = @loop_count + 1
End

--
--finish your own code here
--keep the code between "END" and the end of the procedure unchanged!
--
--END
--
--SET @rows = @@ROWCOUNT
Set @step_id = @step_id + 1;
Set @step_name = N'end';
Set @source_object = Null;
Set @target_object = Null;

Exec logs.usp_ExecutionLog_insert
    @execution_instance_guid = @execution_instance_guid
  , @ssis_execution_id = @ssis_execution_id
  , @sub_execution_id = @sub_execution_id
  , @parent_execution_log_id = @parent_execution_log_id
  , @current_execution_guid = @current_execution_guid
  , @proc_id = @proc_id
  , @proc_schema_name = @proc_schema_name
  , @proc_name = @proc_name
  , @event_info = @event_info
  , @step_id = @step_id
  , @step_name = @step_name
  , @source_object = @source_object
  , @target_object = @target_object;