property.usp_MeasureProperty_set - P
type: P ( stored procedure ), modify_date: 2021-11-28 11:03:38
RepoObject_guid: 318D889D-3250-EC11-8532-A81E8446D5B0
Examples
Example 1. Usage
EXEC [property].[usp_MeasureProperty_set]
@Measure_fullname2 = 'SchemaName.ObjectName'
, @property_name = 'My own property'
, @property_value =
'this additional informations should be part of the documentation
'
Parameters
-
@Measure_guid (uniqueidentifier)
-
@Measure_fullname (nvarchar(261))
-
@Measure_fullname2 (nvarchar(257))
-
@property_name (nvarchar(128))
-
@property_value (nvarchar(max))
sql_modules_definition
property.usp_MeasureProperty_set - P script
/*
<<property_start>>exampleUsage
EXEC [property].[usp_MeasureProperty_set]
@Measure_fullname2 = 'SchemaName.ObjectName'
, @property_name = 'My own property'
, @property_value =
'this additional informations should be part of the documentation
'
<<property_end>>
*/
create Procedure [property].[usp_MeasureProperty_set]
--
@Measure_guid UniqueIdentifier = Null --if @Measure_guid is NULL, then @Measure_fullname or @Measure_fullname2 is used
, @Measure_fullname NVarchar(261) = Null --will be used to find matching @Measure_guid, if @Measure_guid is NULL; use [schema].[TableOrView]
, @Measure_fullname2 NVarchar(257) = Null --will be used to find matching @Measure_guid, if @Measure_guid is NULL; use schema.TableOrView
, @property_name NVarchar(128)
, @property_value NVarchar(Max)
As
Begin
Declare @step_name NVarchar(1000) = Null;
If @Measure_guid Is Null
Set @Measure_guid =
(
Select
Measure_guid
From
repo.Measure
Where
Measure_fullname = @Measure_fullname
) ;
If @Measure_guid Is Null
Set @Measure_guid =
(
Select
Measure_guid
From
repo.Measure
Where
Measure_fullname2 = @Measure_fullname2
) ;
--check existence of @Measure_guid
If Not Exists
(
Select
1
From
repo.Measure
Where
Measure_guid = @Measure_guid
)
Begin
Set @step_name
= Concat (
'Measure_guid does not exist;'
, @Measure_guid
, ';'
, @Measure_fullname
, ';'
, @Measure_fullname2
);
Throw 51001, @step_name, 1;
End;
----insert missing
--Insert property.MeasureProperty
--(
-- Measure_guid
-- , property_name
-- , property_value
--)
--Select
-- S.Measure_guid
-- , S.property_name
-- , S.property_value
--From
--(
-- Select
-- Measure_guid = @Measure_guid
-- , property_name = @property_name
-- , property_value = @property_value
--) As S
--Where
-- Not Exists
--(
-- Select
-- 1
-- From
-- property.MeasureProperty As T
-- Where
-- T.Measure_guid = S.Measure_guid
-- And T.property_name = S.property_name
--);
----update changed
--Update
-- T
--Set
-- T.property_value = S.property_value
--From
--(
-- Select
-- Measure_guid = @Measure_guid
-- , property_name = @property_name
-- , property_value = @property_value
--) As S
-- Inner Join
-- property.MeasureProperty As T
-- On
-- T.Measure_guid = S.Measure_guid
-- And T.property_name = S.property_name
-- And
-- (
-- T.property_value <> S.property_name
-- Or T.property_value Is Null
-- Or S.property_value Is Null
-- )
Merge property.MeasureProperty As T
Using
(
Select
@Measure_guid
, @property_name
, @property_value
) As S
( Measure_guid, property_name, property_value )
On (
T.Measure_guid = S.Measure_guid
And T.property_name = S.property_name
)
When Matched
Then Update Set
T.property_value = S.property_value
When Not Matched
Then Insert
(
Measure_guid
, property_name
, property_value
)
Values
(
S.Measure_guid
, S.property_name
, S.property_value
)
----Output issue because of calculated column in target table
--Output
-- deleted.*
-- , $ACTION
-- , inserted.*
;
End;