property.usp_RepoObject_Inheritance - P
type: P ( stored procedure ), modify_date: 2021-11-17 19:24:15
RepoObject_guid: 63B33A4A-426D-EB11-84E2-A81E8446D5B0
Parameters
-
@execution_instance_guid (uniqueidentifier)
-
@ssis_execution_id (bigint)
-
@sub_execution_id (int)
-
@parent_execution_log_id (bigint)
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;