· 6 years ago · Dec 23, 2019, 01:12 AM
1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spDacomUpdateMeterCOMParam]') AND type in (N'P', N'PC'))
2DROP PROCEDURE [dbo].[spDacomUpdateMeterCOMParam]
3GO
4/******************************************************************************************************************************
5spDacomUpdateMeterCOMParam
6
7Author: Jay Rasmussen
8Version: 8.5.6
9VerDate: 2002/01/18
10
11Params:
12
13 @atvcServerName VARCHAR(60), -- Meter COM server name
14 @atvcInstType VARCHAR(20), -- Instrument type string
15 @atvcRevision VARCHAR(100), -- Meter COM revision string
16 @atvcTCPport VARCHAR(30) -- TCP Port name
17
18Result Set:
19
20 none
21
22Remarks:
23
24 This procedure updates the tbMeterCOM table
25
26 2001/09/14 v1.0.0 initial version
27 2001/09/21 v5.6.0 compliance with QCM DB sep 20 2001
28 2001/09/23 v6.3.0 set code for string case comparison in tbServer
29 2001/11/26 v7.4.0 compliance with QCM DB nov 26 2001
30 2001/12/18 v8.5.1 *** QCM3 final release code Dec 2001 ***
31 2002/01/14 v8.5.2 add comments
32******************************************************************************************************************************/
33
34CREATE PROC spDacomUpdateMeterCOMParam
35 @atvcServerName VARCHAR(60), -- Meter COM server name
36 @atvcInstType VARCHAR(20), -- Instrument type string
37 @atvcRevision VARCHAR(100), -- Meter COM revision string
38 @atvcTCPport VARCHAR(30) -- TCP Port name
39AS
40
41SET NOCOUNT ON
42SET ANSI_WARNINGS OFF
43
44--print 'DBG: spDacomSetupTransferTables tbRawTransferElement Start' + CONVERT(VARCHAR(40),GETDATE(),9)
45
46-- set return code of 0 for success
47DECLARE @i2Return SMALLINT
48SET @i2Return = 0
49
50----------------------------------------------------
51-- Lookup miscellaneous keys needed for update
52----------------------------------------------------
53DECLARE @i2InstType AS SMALLINT
54SET @i2InstType =
55(
56 SELECT
57 pki2InstType
58 FROM
59 tbInstType
60 WHERE
61 atvcInstType = @atvcInstType
62)
63
64DECLARE @i2Server SMALLINT
65SET @i2Server =
66(
67 SELECT
68 pki2Server
69 FROM
70 tbServer
71 WHERE
72 UPPER(atvcServerName) = UPPER(@atvcServerName)
73)
74
75-- Update the Meter COM table if a valid servername and inst type have been passed to this procedure
76IF (@i2InstType IS NOT NULL) AND (@i2Server IS NOT NULL)
77BEGIN
78 IF EXISTS (SELECT pki2Server, pki2InstType FROM tbMeterCOM WHERE (pki2Server = @i2Server) AND (pki2InstType = @i2InstType))
79 BEGIN
80 UPDATE
81 tbMeterCOM
82 SET
83 tbMeterCOM.atvcRevisionNo = ISNULL(@atvcRevision,tbMeterCOM.atvcRevisionNo),
84 tbMeterCOM.atvcTCPport = ISNULL(@atvcTCPport,tbMeterCOM.atvcTCPport)
85 FROM
86 tbMeterCOM
87 WHERE
88 tbMeterCOM.pki2Server = @i2Server AND
89 tbMeterCOM.pki2InstType = @i2InstType
90 END
91 ELSE
92 BEGIN
93 INSERT
94 tbMeterCOM
95 SELECT
96 @i2Server,
97 @i2InstType,
98 @atvcRevision,
99 @atvcTCPport
100 END
101END
102
103-- bad inst type
104SET @i2Return = 1
105IF (@i2InstType IS NULL)
106BEGIN
107 SET @i2Return = -1
108END
109
110-- bad server name
111IF (@i2Server IS NULL)
112BEGIN
113 SET @i2Return = -2
114END
115
116-- bad inst type and server name
117IF (@i2InstType IS NULL) AND (@i2Server IS NULL)
118BEGIN
119 SET @i2Return = -3
120END
121
122
123GO