USE [DXQcontrol_VWAnhuiCP2PS01_Operational_W]
GO
/****** Object: StoredProcedure [dbo].[DS_SP_AlsAlarmsInsert] Script Date: 7/25/2025 9:45:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 2005-08-22, SDR: Stores a new alarm in database
-- 2006-02-28, JM: read the priority from the DS_AlsTags_TAB, if possible
-- 2024-01-30, LZ: extend argument @arg_dynItemId from 11 to 17 digits
-- internal component message number extend from 4 to 10 digits
ALTER PROCEDURE [dbo].[DS_SP_AlsAlarmsInsert]
@arg_utc_dts_new_on DATETIME,
@arg_computer NVARCHAR(16),
@arg_ionode NVARCHAR(32),
@arg_ioitem NVARCHAR(64),
@arg_text_id NVARCHAR(32),
@arg_priority INT,
@arg_localTimeDiff INT, -- offset in seconds from UTC to local time
-- the following parameters are only valid for dynamic messages;
-- for dynamic messages the meaning of "@arg_dynItemId" is the same
-- like "@arg_ioitem" in case of static messages;
-- all of these parameters are an empty string ('') for static messages
@arg_dynItemId NVARCHAR(17), -- 17 character which includes:
-- 1..4 : component
-- 5..7 : device identifier
-- 8..17 : internal component message number
@arg_deviceId NVARCHAR(3), -- character 5..7 of @arg_dynItemId
@arg_message NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;
-- constants
DECLARE @procName NVARCHAR(128) = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID);
DECLARE @lengthDynItemId TINYINT = 17; -- length of parameter @arg_dynItemId; use the variable instead of fix length inside the code
-- variables
DECLARE @iError INT;
DECLARE @iRowCount INT;
DECLARE @szErrType NVARCHAR(32);
DECLARE @szErrMsg NVARCHAR(MAX);
DECLARE @bDynAlarm TINYINT;
DECLARE @bDynTagBreak TINYINT;
DECLARE @szDynTextId NVARCHAR(32);
DECLARE @szDynLamEnglish NVARCHAR(128);
DECLARE @szDynToken NVARCHAR(128);
DECLARE @bDynParamIndex TINYINT;
DECLARE @sDynStartPos SMALLINT;
DECLARE @sDynEndPos SMALLINT;
DECLARE @ioitem NVARCHAR(64);
DECLARE @cfg_inst_structure BIGINT;
DECLARE @areakey NVARCHAR(96);
DECLARE @plant_type CHAR(1);
DECLARE @class CHAR(2);
DECLARE @shiftnr TINYINT;
DECLARE @wtmid INT;
DECLARE @iAlsTagsPriority INT;
DECLARE @local_dts_new_on DATETIME;
DECLARE @tTypIdOfShift TINYINT;
DECLARE @szSyncAlarms NVARCHAR(8);
-------------------------------------------------------------------------------
-- Check the input parameters
IF (@arg_utc_dts_new_on IS NULL OR
@arg_computer IS NULL OR
@arg_ionode IS NULL OR
@arg_ioitem IS NULL OR
@arg_priority IS NULL OR
@arg_localTimeDiff IS NULL)
BEGIN
EXEC [dbo].[DS_SP_WriteToDSErrorLog] @procName, N'ERROR', N'Invalid parameters';
RETURN 0;
END;
IF (LEN(@arg_computer) = 0 OR LEN(@arg_ionode) = 0 OR LEN(@arg_ioitem) = 0)
BEGIN
EXEC [dbo].[DS_SP_WriteToDSErrorLog] @procName, N'ERROR', N'Invalid parameters';
RETURN 0;
END;
SET @szErrType = N'WARNING';
SET @szErrMsg = N'';
SET @cfg_inst_structure = NULL;
-------------------------------------------------------------------------------
-- check whether this is a dynamic alarm
SET @bDynAlarm = 0;
SET @ioitem = @arg_ioitem;
IF (@arg_dynItemId IS NOT NULL AND @arg_deviceId IS NOT NULL)
BEGIN
IF (LEN(@arg_dynItemId) > 0 AND LEN(@arg_deviceId) > 0)
BEGIN
SET @bDynAlarm = 1;
SET @ioitem = @arg_dynItemId;
END;
END; -- if (@arg_dynItemId is not null and @arg_deviceId is not null)
IF (@bDynAlarm = 0)
BEGIN
SELECT TOP(1)
@cfg_inst_structure = [cfg_inst_structure],
@areakey = [areakey],
@plant_type = [plant_type],
@iAlsTagsPriority = [priority],
@class = [class]
FROM [dbo].[DS_AlsTags_TAB]
WHERE ([ionode] = @arg_ionode AND [ioitem] = @ioitem)
ORDER BY [id] DESC;
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT;
END;
ELSE
BEGIN
BEGIN TRANSACTION;
SET @iRowCount = 0;
SELECT TOP(1)
@cfg_inst_structure = ISNULL([DYN].[cfg_inst_structure], [DATT].[cfg_inst_structure]),
@areakey = ISNULL([DYN].[areakey],[DATT].[areakey]),
@plant_type = ISNULL([DYN].[plant_type],[DATT].[plant_type]),
@iAlsTagsPriority = ISNULL([DYN].[priority],[DATT].[priority]),
@class = ISNULL([DYN].[class],[DATT].[class]),
@iRowCount = (CASE WHEN [DYN].[ionode] IS NULL THEN 0 ELSE 1 END)
FROM [dbo].[DS_AlsTags_TAB] [DATT] WITH (ROWLOCK XLOCK)
RIGHT JOIN [dbo].[DS_AlsDynTags_TAB] [DYN] WITH (ROWLOCK XLOCK)
ON [DYN].[ionode] = [DATT].[ionode] AND [DYN].[ioitem] = [DATT].[ioitem]
WHERE ([DYN].[ionode] = @arg_ionode AND [DYN].[ioitem] = @ioitem)
ORDER BY [DYN].[id] DESC;
IF @iRowCount = 0
BEGIN
-- in case of dynamic tags the arg_priority is used
INSERT INTO [dbo].[DS_AlsDynTags_TAB]
([ionode], [ioitem],
[plant_id], [plant_type], [equipment_id], [devicename],
[groupkey], [cfg_inst_structure], [text_id],
[class], [location],
[active], [priority], [log_enabled], [areakey])
SELECT @arg_ionode, @ioitem,
[plant_id], [plant_type], [equipment_id], @arg_deviceId,
[groupkey], [cfg_inst_structure], @arg_text_id,
[class], [location],
[active], @arg_priority, [log_enabled], [areakey]
FROM [dbo].[DS_AlsTags_TAB]
WHERE ([ionode] = @arg_ionode AND [ioitem] = @arg_ioitem);
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT;
SELECT TOP(1)
@cfg_inst_structure = ISNULL([DYN].[cfg_inst_structure], [DATT].[cfg_inst_structure]),
@areakey = ISNULL([DYN].[areakey],[DATT].[areakey]),
@plant_type = ISNULL([DYN].[plant_type],[DATT].[plant_type]),
@iAlsTagsPriority = ISNULL([DYN].[priority],[DATT].[priority]),
@class = ISNULL([DYN].[class],[DATT].[class]),
@iRowCount = (CASE WHEN [DYN].[ionode] IS NULL THEN 0 ELSE 1 END)
FROM [dbo].[DS_AlsTags_TAB] [DATT] WITH (ROWLOCK XLOCK)
RIGHT JOIN [dbo].[DS_AlsDynTags_TAB] [DYN] WITH (ROWLOCK XLOCK)
ON [DYN].[ionode] = [DATT].[ionode] AND [DYN].[ioitem] = [DATT].[ioitem]
WHERE ([DYN].[ionode] = @arg_ionode AND [DYN].[ioitem] = @ioitem)
ORDER BY [DYN].[id] DESC;
END; -- IF @iRowCount = 0
COMMIT TRANSACTION;
END; -- else of 'if (@bDynAlarm = 0)'
IF (@iError <> 0 OR @iRowCount <> 1)
BEGIN
SET @szErrMsg = N'AlarmTag does not exist for node '+ @arg_ionode + N' and item ' + @ioitem
+ N';error=' + CAST(@iError AS NVARCHAR(16)) + N';row count=' + CAST(@iRowCount AS NVARCHAR(16));
GOTO afterTransaction;
END;
-------------------------------------------------------------------------------
-- If this is a 'dynamic' or a 'dynamic direct' alarm then store
-- the message text direct in the attribute 'message' of the new alarm
-- Note: because of that direct storage only one language is supported
-- in the attribute 'message' (the default language is 'en')
SET @szDynLamEnglish = N'';
IF (LEN(ISNULL(@arg_message,N'')) > 0)
BEGIN
IF (@bDynAlarm = 0 OR LEN(@arg_dynItemId) <> @lengthDynItemId)
BEGIN
-- this is a dynamic direct alarm, therefore we can
-- take the native received message
SET @szDynLamEnglish = @arg_message;
END;
ELSE
BEGIN
SET @szDynTextId = N'T02_' + SUBSTRING(@arg_dynItemId,1,4) + N'_' + SUBSTRING(@arg_dynItemId,8,10);
IF LEN(@arg_text_id) >= 8
BEGIN
-- check whether we the can use directly the text_id to search for the
-- corresponding language format string
-- we assume that the text_id is valid if the component (4 characters)
-- and the text library number (4 characters) are included in the text_id
IF (CHARINDEX(SUBSTRING(@arg_dynItemId,1,4), @arg_text_id, 1) > 0 AND
CHARINDEX(SUBSTRING(@arg_dynItemId,8,10), @arg_text_id, 1) > 0)
BEGIN
SET @szDynTextId = @arg_text_id;
END;
END;
SELECT TOP(1) @szDynLamEnglish = [en] FROM [dbo].[DS_LamTexts_TAB]
WHERE ([text_id] = @szDynTextId AND [sub_id] = N'MESSAGE')
ORDER BY [sub_id] ASC;
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT;
IF (@iError = 0 AND @iRowCount = 1 AND LEN(ISNULL(@szDynLamEnglish,N'')) > 0)
BEGIN
SET @bDynParamIndex = 0;
SET @sDynStartPos = 1;
SET @bDynTagBreak = 0;
WHILE (@bDynTagBreak = 0)
BEGIN
SET @bDynParamIndex = @bDynParamIndex + 1;
SET @sDynEndPos = CHARINDEX(N';', @arg_message, @sDynStartPos);
IF (@sDynEndPos = 0 OR @sDynEndPos >= 128 OR @bDynParamIndex >= 9)
BEGIN
SET @sDynEndPos = LEN(@arg_message) + 1;
SET @bDynTagBreak = 1;
END;
SET @szDynToken = N'';
IF ((@sDynEndPos - @sDynStartPos) > 0)
BEGIN
SET @szDynToken = RTRIM(LTRIM(SUBSTRING(@arg_message, @sDynStartPos, @sDynEndPos - @sDynStartPos)));
END;
IF @bDynParamIndex = 1
BEGIN
IF (LEN(@szDynToken) = 3)
SET @szDynLamEnglish = REPLACE(@szDynLamEnglish, N'%id', @szDynToken);
ELSE
SET @szDynLamEnglish = REPLACE(@szDynLamEnglish, N'%id', @arg_deviceId);
END;
ELSE
BEGIN
SET @szDynLamEnglish = REPLACE(@szDynLamEnglish, N'%s' + CAST((@bDynParamIndex-1) AS NVARCHAR(16)), @szDynToken);
END;
SET @sDynStartPos = @sDynEndPos + 1;
IF (@sDynStartPos > 128)
BEGIN
SET @bDynTagBreak = 1;
END;
END; -- WHILE (@bDynTagBreak = 0)
END; --if (@iError = 0 and @iRowCount = 1 and len(isnull(@szDynLamEnglish,0)) > 0)
ELSE
BEGIN
-- there exists no format string for this dynamic alarm
-- therefore take the native received message
SET @szDynLamEnglish = @arg_message;
END;
END; -- else of 'IF (@bDynAlarm = 0 OR LEN(@arg_dynItemId) <> 11)'
END; --IF (LEN(ISNULL(@arg_message,N'')) > 0)
-------------------------------------------------------------------------------
-- Insert a new dataset for the alarm if there is no open alarm registers for this tag
-- If an alarm bit is operated by more than one AlarmServer then
-- the following constallation can occure:
-- AlarmServer 1 inserts a new alarm. This alarm went few seconds later.
-- Now AlarmServer 2 process the same alarm and because of this the
-- same alarm is stored duplicated in database. The probability of
-- this occurance is higher for shorter alarm durations.
--
-- Configuration to synchronize multiple AlarmServer with same alarm items
-- * DS_GlobalParameter_TAB: set the value to "yes" for parameter
-- "SynchronizeMultipleAlarmServer" and category "AlarmServer"
-- List of programs which call the procedures DS_SP_AlsAlarmsInsert and DS_SP_AlsAlarmsUpdate
-- * the AlarmServer
-- - there exists max one AlarmServer per computer
-- - there exist max two database connections per AlarmServer (primary and secondary database)
-- (the primary and secondary database have to be different)
-- - the alarms within the BUF files are processed in FIFO order by the C++ database DLL
-- * the procedures DS_SP_PBS_AlarmHandling, DS_SP_WOPC_AlarmHandling
--
SELECT TOP(1) @szSyncAlarms = [value] FROM [dbo].[DS_GlobalParameter_TAB] WITH (NOLOCK)
WHERE [category] = N'AlarmServer' AND [parameter] = N'SynchronizeMultipleAlarmServer' AND [active] = 1
ORDER BY [id] DESC;
IF @@ROWCOUNT = 0 SET @szSyncAlarms = N'no';
IF @szSyncAlarms = N'yes'
BEGIN
-- Enter critical section
BEGIN TRANSACTION;
IF EXISTS(SELECT 1 FROM [dbo].[DS_AlsAlarmsExclusiveLock_TAB] [DAAELT] WITH (ROWLOCK XLOCK HOLDLOCK)
WHERE [DAAELT].[ionode] = @arg_ionode
AND [DAAELT].[ioitem] = @ioitem)
BEGIN
UPDATE [dbo].[DS_AlsAlarmsExclusiveLock_TAB] WITH (ROWLOCK XLOCK HOLDLOCK)
SET [maxDate] = @arg_utc_dts_new_on
WHERE [ionode] = @arg_ionode
AND [ioitem] = @ioitem
AND [maxDate] < @arg_utc_dts_new_on;
END;
ELSE
BEGIN
INSERT INTO [dbo].[DS_AlsAlarmsExclusiveLock_TAB]([ionode],[ioitem],[maxDate])
VALUES (@arg_ionode,@ioitem,@arg_utc_dts_new_on);
END;
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT;
COMMIT TRANSACTION;
IF @iError <> 0
BEGIN
SET @szErrType = N'ERROR';
SET @szErrMsg = N'Can not select from table DS_AlsAlarmsExclusiveLock_TAB for node '+ @arg_ionode + N' and item ' + @ioitem
+ N';error=' + CAST(@iError AS NVARCHAR(16));
END;
IF @iRowCount = 0
BEGIN
GOTO afterTransaction;
END;
END; -- if @szSyncAlarms = N'yes'
ELSE
BEGIN
SELECT @iRowCount = 1, @iError = 0;
END;
-- write alarm logging
IF @iRowCount = 1 AND @iError = 0
BEGIN
SET @wtmid = -1;
-- get the work time model ID for this alarm
IF (@cfg_inst_structure IS NOT NULL)
BEGIN
SELECT TOP(1) @wtmid = [WTM_ID] FROM [dbo].[DS_CfgInstallationStructure_TAB]
WHERE ([ID] = @cfg_inst_structure)
ORDER BY [ID] DESC;
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT;
IF (@iError <> 0 OR @iRowCount <> 1)
BEGIN
SET @wtmid = -1;
END;
END; -- if (@cfg_inst_structure is not null)
-- get the actual shift number
SET @local_dts_new_on = DATEADD(ss,@arg_localTimeDiff,@arg_utc_dts_new_on);
EXEC @shiftnr = [dbo].[DS_SP_GetShiftDetailsForDateEvt] @local_dts_new_on, @wtmid, @tTypIdOfShift OUTPUT, 1;
-- 2022-10-20, debiezala
-- use merge instead of insert to prevent double entries
-- independant when how many AlarmServers are running
MERGE INTO [dbo].[DS_AlsAlarms_TAB] AS [t]
USING (VALUES
(@arg_ionode, @ioitem)
) AS [s] ([ionode], [ioitem])
ON ([t].[ionode] = [s].[ionode] AND [t].[ioitem] = [s].[ioitem] AND [t].[utc_dts_gone_off] IS NULL)
WHEN NOT MATCHED THEN
INSERT ([utc_dts_new_on],
[local_dts_new_on],
[shift_nr],
[computer],
[text_id],
[ionode],
[ioitem],
[message],
[priority],
[areakey],
[plant_type],
[class])
VALUES (@arg_utc_dts_new_on,
@local_dts_new_on,
@shiftnr,
@arg_computer,
@arg_text_id,
@arg_ionode,
@ioitem,
@szDynLamEnglish,
ISNULL(@iAlsTagsPriority, @arg_priority),
@areakey,
@plant_type,
@class);
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT;
IF @iError <> 0
BEGIN
SET @szErrType = N'ERROR';
SET @szErrMsg = N'Can not insert alarm message for node '+ @arg_ionode + N' and item ' + @ioitem
+ N';error=' + CAST(@iError AS NVARCHAR(16)) + N';row count=' + CAST(@iRowCount AS NVARCHAR(16));
GOTO afterTransaction;
END;
END; -- if @iRowCount = 0 and @iError = 0
afterTransaction:
IF @szErrMsg <> N''
BEGIN
EXEC [dbo].[DS_SP_WriteToDSErrorLog] @procName, @szErrType, @szErrMsg;
END;
RETURN 1;
END;
分析以上存储过程
最新发布
本文介绍如何在SQL Server中使用DECLARE语句声明局部变量和游标变量，并提供了使用SET和SELECT语句为变量赋值的方法。此外，还详细说明了如何定义表数据类型，包括列定义、数据类型及约束。
扫一扫
8672
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
