reference.usp_RepoObjectSource_virtual_set - P

type: P ( stored procedure ), modify_date: 2021-10-02 13:23:34

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

Description

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

Examples

Example 1. Usage
EXEC reference.usp_RepoObjectSource_virtual_set
    @RepoObject_fullname2 = 'SchemaName.ObjectName'
  , @Source_RepoObject_fullname2 = 'SourceSchemaName.SourceObjectName';

EXEC reference.usp_RepoObjectSource_virtual_set
    @RepoObject_fullname = '[SchemaName].[ObjectName]'
  , @Source_RepoObject_fullname = '[SourceSchemaName].[SourceObjectName]';

Parameters

  • @RepoObject_guid (uniqueidentifier)

  • @RepoObject_fullname (nvarchar(520))

  • @RepoObject_fullname2 (nvarchar(386))

  • @Source_RepoObject_guid (uniqueidentifier)

  • @Source_RepoObject_fullname (nvarchar(520))

  • @Source_RepoObject_fullname2 (nvarchar(386))

Entity Diagram

entity-reference.usp_repoobjectsource_virtual_set

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.usp_repoobjectsource_virtual_set

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.usp_repoobjectsource_virtual_set

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.usp_repoobjectsource_virtual_set

sql_modules_definition

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

<<property_start>>exampleUsage
EXEC reference.usp_RepoObjectSource_virtual_set
    @RepoObject_fullname2 = 'SchemaName.ObjectName'
  , @Source_RepoObject_fullname2 = 'SourceSchemaName.SourceObjectName';

EXEC reference.usp_RepoObjectSource_virtual_set
    @RepoObject_fullname = '[SchemaName].[ObjectName]'
  , @Source_RepoObject_fullname = '[SourceSchemaName].[SourceObjectName]';
<<property_end>>
*/
CREATE Procedure [reference].[usp_RepoObjectSource_virtual_set]
    --
    @RepoObject_guid             UniqueIdentifier = Null --if @RepoObject_guid is NULL, then @RepoObject_fullname or @RepoObject_fullname2 are used
  , @RepoObject_fullname         NVarchar(520)    = Null --can be used to define @RepoObject_guid; use '[SchemaName].[ObjectName]'
  , @RepoObject_fullname2        NVarchar(386)    = Null --can be used to define @RepoObject_guid; use 'SchemaName.ObjectName'
  , @Source_RepoObject_guid      UniqueIdentifier = Null --if @Source_RepoObject_guid is NULL, then @Source_RepoObject_fullname or @Source_RepoObject_fullname2 are used
  , @Source_RepoObject_fullname  NVarchar(520)    = Null --can be used to define @Source_RepoObject_guid; use '[SchemaName].[ObjectName]'
  , @Source_RepoObject_fullname2 NVarchar(386)    = Null --can be used to define @Source_RepoObject_guid; use 'SchemaName.ObjectName'
As
Begin
    Declare @step_name NVarchar(1000) = Null;

    If @RepoObject_guid Is Null
        Set @RepoObject_guid =
    (
        Select
            RepoObject_guid
        From
            repo.RepoObject roc
        Where
            roc.RepoObject_fullname = @RepoObject_fullname
    )   ;

    If @RepoObject_guid Is Null
        Set @RepoObject_guid =
    (
        Select
            RepoObject_guid
        From
            repo.RepoObject roc
        Where
            roc.RepoObject_fullname2 = @RepoObject_fullname2
    )   ;

    --check existence of @RepoObject_guid
    If Not Exists
    (
        Select
            1
        From
            repo.RepoObject
        Where
            RepoObject_guid = @RepoObject_guid
    )
    Begin
        Set @step_name
            = Concat (
                         'RepoObject_guid does not exist;'
                       , @RepoObject_guid
                       , ';'
                       , @RepoObject_fullname
                       , ';'
                       , @RepoObject_fullname2
                     );

        Throw 51001, @step_name, 1;
    End;

    If @Source_RepoObject_guid Is Null
        Set @Source_RepoObject_guid =
    (
        Select
            RepoObject_guid
        From
            repo.RepoObject roc
        Where
            roc.RepoObject_fullname = @Source_RepoObject_fullname
    )   ;

    If @Source_RepoObject_guid Is Null
        Set @Source_RepoObject_guid =
    (
        Select
            RepoObject_guid
        From
            repo.RepoObject roc
        Where
            roc.RepoObject_fullname2 = @Source_RepoObject_fullname2
    )   ;

    --check existence of @RepoObject_guid
    If Not Exists
    (
        Select
            1
        From
            repo.RepoObject
        Where
            RepoObject_guid = @Source_RepoObject_guid
    )
    Begin
        Set @step_name
            = Concat (
                         'Source_RepoObject_guid does not exist;'
                       , @Source_RepoObject_guid
                       , ';'
                       , @Source_RepoObject_fullname
                       , ';'
                       , @Source_RepoObject_fullname2
                     );

        Throw 51002, @step_name, 1;
    End;

    If Not Exists
    (
        Select
            1
        From
            reference.RepoObjectSource_virtual
        Where
            RepoObject_guid            = @RepoObject_guid
            And Source_RepoObject_guid = @Source_RepoObject_guid
    )
        Insert Into reference.RepoObjectSource_virtual
        Values
            (
                @RepoObject_guid
              , @Source_RepoObject_guid
            );
End;