reference.usp_RepoObjectColumnSource_virtual_set - P

type: P ( stored procedure ), modify_date: 2022-01-05 18:06:36

RepoObject_guid: 7D9A3468-7DA6-EB11-84FB-A81E8446D5B0

Description

insert entries into reference.RepoObjectColumnSource_virtual - U, using guid, fullname or fullname2

Examples

Example 1. Usage
EXEC reference.usp_RepoObjectColumnSource_virtual_set
    @RepoObjectColumn_fullname2 = 'SchemaName.ObjectName.ColumnName'
  , @Source_RepoObjectColumn_fullname2 = 'SourceSchemaName.SourceObjectName.SourceColumnName';

EXEC reference.usp_RepoObjectColumnSource_virtual_set
    @RepoObjectColumn_fullname = '[SchemaName].[ObjectName].[ColumnName]'
  , @Source_RepoObjectColumn_fullname = '[SourceSchemaName].[SourceObjectName].[SourceColumnName]';

Parameters

  • @RepoObjectColumn_guid (uniqueidentifier)

  • @RepoObjectColumn_fullname (nvarchar(520))

  • @RepoObjectColumn_fullname2 (nvarchar(386))

  • @Source_RepoObjectColumn_guid (uniqueidentifier)

  • @Source_RepoObjectColumn_fullname (nvarchar(520))

  • @Source_RepoObjectColumn_fullname2 (nvarchar(386))

Entity Diagram

entity-reference.usp_repoobjectcolumnsource_virtual_set

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.usp_repoobjectcolumnsource_virtual_set

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.usp_repoobjectcolumnsource_virtual_set

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.usp_repoobjectcolumnsource_virtual_set

sql_modules_definition

reference.usp_RepoObjectColumnSource_virtual_set - P script
/*
<<property_start>>Description
insert entries into xref:sqldb:reference.repoobjectcolumnsource_virtual.adoc[], using guid, fullname or fullname2
<<property_end>>

<<property_start>>exampleUsage
EXEC reference.usp_RepoObjectColumnSource_virtual_set
    @RepoObjectColumn_fullname2 = 'SchemaName.ObjectName.ColumnName'
  , @Source_RepoObjectColumn_fullname2 = 'SourceSchemaName.SourceObjectName.SourceColumnName';

EXEC reference.usp_RepoObjectColumnSource_virtual_set
    @RepoObjectColumn_fullname = '[SchemaName].[ObjectName].[ColumnName]'
  , @Source_RepoObjectColumn_fullname = '[SourceSchemaName].[SourceObjectName].[SourceColumnName]';
<<property_end>>
*/
CREATE Procedure [reference].[usp_RepoObjectColumnSource_virtual_set]
    --
    @RepoObjectColumn_guid             UniqueIdentifier = Null --if @RepoObjectColumn_guid is NULL, then @RepoObjectColumn_fullname or @RepoObjectColumn_fullname2 are used
  , @RepoObjectColumn_fullname         NVarchar(520)    = Null --can be used to define @RepoObjectColumn_guid; use '[SchemaName].[ObjectName].[ColumnName]'
  , @RepoObjectColumn_fullname2        NVarchar(386)    = Null --can be used to define @RepoObjectColumn_guid; use 'SchemaName.ObjectName.ColumnName'
  , @Source_RepoObjectColumn_guid      UniqueIdentifier = Null --if @Source_RepoObjectColumn_guid is NULL, then @Source_RepoObjectColumn_fullname or @Source_RepoObjectColumn_fullname2 are used
  , @Source_RepoObjectColumn_fullname  NVarchar(520)    = Null --can be used to define @Source_RepoObjectColumn_guid; use '[SchemaName].[ObjectName].[ColumnName]'
  , @Source_RepoObjectColumn_fullname2 NVarchar(386)    = Null --can be used to define @Source_RepoObjectColumn_guid; use 'SchemaName.ObjectName.ColumnName'
As
Begin
    Declare @step_name NVarchar(1000) = Null;

    If @RepoObjectColumn_guid Is Null
        Set @RepoObjectColumn_guid =
    (
        Select
            roc.RepoObjectColumn_guid
        From
            repo.RepoObjectColumn_gross As roc
        Where
            roc.RepoObjectColumn_fullname = @RepoObjectColumn_fullname
    )   ;

    If @RepoObjectColumn_guid Is Null
        Set @RepoObjectColumn_guid =
    (
        Select
            roc.RepoObjectColumn_guid
        From
            repo.RepoObjectColumn_gross As roc
        Where
            roc.RepoObjectColumn_fullname2 = @RepoObjectColumn_fullname2
    )   ;

    --check existence of @RepoObjectColumn_guid
    If Not Exists
    (
        Select
            1
        From
            repo.RepoObjectColumn
        Where
            RepoObjectColumn_guid = @RepoObjectColumn_guid
    )
    Begin
        Set @step_name
            = Concat (
                         'RepoObjectColumn_guid does not exist;'
                       , @RepoObjectColumn_guid
                       , ';'
                       , @RepoObjectColumn_fullname
                       , ';'
                       , @RepoObjectColumn_fullname2
                     );

        Throw 51001, @step_name, 1;
    End;

    If @Source_RepoObjectColumn_guid Is Null
        Set @Source_RepoObjectColumn_guid =
    (
        Select
            roc.RepoObjectColumn_guid
        From
            repo.RepoObjectColumn_gross As roc
        Where
            roc.RepoObjectColumn_fullname = @Source_RepoObjectColumn_fullname
    )   ;

    If @Source_RepoObjectColumn_guid Is Null
        Set @Source_RepoObjectColumn_guid =
    (
        Select
            roc.RepoObjectColumn_guid
        From
            repo.RepoObjectColumn_gross As roc
        Where
            roc.RepoObjectColumn_fullname2 = @Source_RepoObjectColumn_fullname2
    )   ;

    --check existence of @RepoObjectColumn_guid
    If Not Exists
    (
        Select
            1
        From
            repo.RepoObjectColumn
        Where
            RepoObjectColumn_guid = @Source_RepoObjectColumn_guid
    )
    Begin
        Set @step_name
            = Concat (
                         'Source_RepoObjectColumn_guid does not exist;'
                       , @Source_RepoObjectColumn_guid
                       , ';'
                       , @Source_RepoObjectColumn_fullname
                       , ';'
                       , @Source_RepoObjectColumn_fullname2
                     );

        Throw 51002, @step_name, 1;
    End;

    If Not Exists
    (
        Select
            1
        From
            reference.RepoObjectColumnSource_virtual
        Where
            RepoObjectColumn_guid            = @RepoObjectColumn_guid
            And Source_RepoObjectColumn_guid = @Source_RepoObjectColumn_guid
    )
        Insert Into reference.RepoObjectColumnSource_virtual
        Values
            (
                @RepoObjectColumn_guid
              , @Source_RepoObjectColumn_guid
            );
End;