-
Notifications
You must be signed in to change notification settings - Fork 0
/
uspAddMissingCustomPropertyForDevice.sql
107 lines (93 loc) · 2.42 KB
/
uspAddMissingCustomPropertyForDevice.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
USE [CM_LAB]
GO
/****** Object: StoredProcedure [automation].[uspAddMissingCustomPropertyForDevice] Script Date: 1/10/2024 3:50:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [automation].[uspAddMissingCustomPropertyForDevice]
@pintResourceId INT,
@pintPropertyId INT,
@pstrCreatedBy VARCHAR(255),
@pstrCreatedDate DATETIME
AS
BEGIN
DECLARE
@PropertyId INT,
@ResourceId INT,
@AlreadyExist BIT = 0
BEGIN TRY
-- DOES RESOURCE EXIST?
SELECT
@ResourceId = SMSRS.ItemKey
FROM
dbo.vSMS_R_SYSTEM SMSRS
WHERE
SMSRS.ItemKey = @pintResourceId
-- DOES CUSTOM PROPERTY EXIST?
SELECT
@PropertyId = DER.PropertyId
FROM
dbo.DeviceExtensionRegistration DER
WHERE
DER.PropertyId = @pintPropertyId
-- IS THIS DEVICE ALREADY CONFIGURED TO USE THIS CUSTOM PROPERTY?
SELECT
TOP 1 @AlreadyExist = COUNT (DED.ID)
FROM
dbo.DeviceExtensionData DED
WHERE
DED.ResourceId = @pintResourceId
AND DED.PropertyId = @pintPropertyId
BEGIN TRANSACTION;
IF
(
@AlreadyExist = 0
AND @ResourceId IS NOT NULL
AND @PropertyId IS NOT NULL
)
INSERT INTO DeviceExtensionData
(
ResourceId,
PropertyId,
--Value,
CreatedBy,
CreatedDate,
ModifiedBy,
ModifiedDate
)
VALUES
(
@ResourceId,
@PropertyId,
--@PropertyValue,
@pstrCreatedBy,
@pstrCreatedDate,
@pstrCreatedBy,
@pstrCreatedDate
)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- report exception
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
-- Is Transaction uncommittable?
IF(XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state. Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
PRINT N'The transaction is committable. Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH
END
GO