property.usp_RepoSchemaProperty_set - P

type: P ( stored procedure ), modify_date: 2021-09-23 20:11:13

RepoObject_guid: 8028F9B8-521D-EC11-8522-A81E8446D5B0

Description

Examples

Example 1. Usage
EXEC [property].[usp_RepoSchemaProperty_set]
@RepoSchema_name = 'MySchema'
, @property_name = 'MS_Description'
, @property_value =
'description for SchemaName.ObjectName

see details in xref:user-guide:create-update-connect-repo-db.adoc[]
'

Parameters

  • @RepoSchema_guid (uniqueidentifier)

  • @RepoSchema_name (nvarchar(128))

  • @property_name (nvarchar(128))

  • @property_value (nvarchar(max))

Entity Diagram

entity-property.usp_reposchemaproperty_set

References

Object Reference Diagram - 1 1

entity_1_1_objectref-property.usp_reposchemaproperty_set

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-property.usp_reposchemaproperty_set

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-property.usp_reposchemaproperty_set

sql_modules_definition

property.usp_RepoSchemaProperty_set - P script
/*
<<property_start>>exampleUsage
EXEC [property].[usp_RepoSchemaProperty_set]
@RepoSchema_name = 'MySchema'
, @property_name = 'MS_Description'
, @property_value =
'description for SchemaName.ObjectName

see details in xref:user-guide:create-update-connect-repo-db.adoc[]
'
<<property_end>>
*/
CREATE Procedure property.usp_RepoSchemaProperty_set
    --
    @RepoSchema_guid UniqueIdentifier = Null --if @RepoSchema_guid is NULL, then @RepoSchema_name or @RepoSchema_name2 is used
  , @RepoSchema_name NVarchar(128)    = Null --will be used to find matching @RepoSchema_guid, if @RepoSchema_guid is NULL; use [schema].[TableOrView]
  , @property_name   NVarchar(128)
  , @property_value  NVarchar(Max)
As
Begin
    Declare @step_name NVarchar(1000) = Null;

    If @RepoSchema_guid Is Null
        Set @RepoSchema_guid =
    (
        Select
            RepoSchema_guid
        From
            repo.RepoSchema
        Where
            RepoSchema_name = @RepoSchema_name
    )   ;

    --check existence of @RepoSchema_guid
    If Not Exists
    (
        Select
            1
        From
            repo.RepoSchema
        Where
            RepoSchema_guid = @RepoSchema_guid
    )
    Begin
        Set @step_name = Concat ( 'RepoSchema_guid does not exist;', @RepoSchema_guid, ';', @RepoSchema_name );

        Throw 51001, @step_name, 1;
    End;

    Merge property.RepoSchemaProperty As T
    Using
    (
        Select
            @RepoSchema_guid
          , @property_name
          , @property_value
    ) As S
    ( RepoSchema_guid, property_name, property_value )
    On (
           T.RepoSchema_guid = S.RepoSchema_guid
           And T.property_name = S.property_name
       )
    When Matched
        Then Update Set
                 T.property_value = S.property_value
    When Not Matched
        Then Insert
             (
                 RepoSchema_guid
               , property_name
               , property_value
             )
             Values
                 (
                     S.RepoSchema_guid
                   , S.property_name
                   , S.property_value
                 )
    Output
        deleted.*
      , $ACTION
      , inserted.*;
End;