sp3

最新推荐文章于 2025-10-27 22:13:31 发布

原创最新推荐文章于 2025-10-27 22:13:31 发布 · 363 阅读

CC 4.0 BY-SA版权

此过程通过创建SQL查询来获取整数和离散在制品批次事件数据，包括整数和离散在制品批次变量的提取和历史记录插入。

USE [SZ_OpMon]
GO
/****** Object: StoredProcedure [dbo].[AATISsp_GetInSQLEventData2] Script Date: 2014/8/5 10:42:27 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AATISsp_GetInSQLEventData2]
@st NVARCHAR(19)
,@et NVARCHAR(19)
AS
/*************************************************************/
/* Copyright 2004 AATIS, Inc. All Rights Reserved */
/* Author: John Dado*/
/* Rev 1.0 10/28/2004*/
/*modified to get both integer and discrete lot in progress*/
/*************************************************************/
SET NOCOUNT ON

begin try
--print 'in get sql event'
DECLARE @sSQL as VARCHAR(8000)
DECLARE @taglist as VARCHAR(8000)
DECLARE @Vname as NVARCHAR(80)
DECLARE @qt as NVARCHAR(1)
SET @qt = char(39)
SET @taglist = ''
--create a list of the keyanalogvariables
DECLARE csrVnameI CURSOR LOCAL FOR
  (select
   DISTINCT VariableName
  from
   AATISvw_EventVariables a
  Inner Join
   Runtime.dbo.Tag b
  On
   b.TagName = a.variablename
  inner join
   AATIS_ProductionMachines c
  on
   a.MachineKey = c.MachineKey
   and c.MachineType = 'IntegerLotInProgress'
  )
  OPEN csrVnameI
  FETCH NEXT FROM csrVnameI INTO @vname
  WHILE @@FETCH_STATUS = 0
  BEGIN
--1000 char = appx 40 tags
   set @taglist = @taglist + @qt+ @vname + @qt + ','
   IF LEN(@taglist) > 1000 --XP_DISCRETEHISTORYDELTA DOES NOT HANDLE INFINITE TAGS
   BEGIN
    SET @taglist = dbo.AATISfn_ClearComma(@taglist)
    SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_AnalogHistoryDelta '
    + @qt+@st + @qt
    + ','+@qt+ @et + @qt
    + ',1000'
    + ',' + @taglist
  --print '[AATISsp_GetInSQLEventData2] ' + @ssql
    EXECUTE (@sSQL)
    SET @taglist = ''
   END
   FETCH NEXT FROM csrVnameI INTO @vname
--select @vname
  END
CLOSE csrVnameI
DEALLOCATE csrVnameI
IF LEN(@taglist) > 0 --GET THE REMAINING TAGS IF ANY
BEGIN
  SET @taglist = dbo.AATISfn_ClearComma(@taglist)
  SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_AnalogHistoryDelta '
  + @qt+@st + @qt
  + ','+@qt+ @et + @qt
  + ',1000'
  + ',' + @taglist
  --print @ssql
  EXECUTE (@sSQL)
END

SET @taglist = '' --now get the discrete lot in progress machine events

DECLARE csrVnameD CURSOR LOCAL FOR
  (select
   DISTINCT VariableName
  from
   AATISvw_EventVariables a
  Inner Join
   Runtime.dbo.Tag b
  On
   b.TagName = a.variablename
  inner join
   AATIS_ProductionMachines c
  on
   a.MachineKey = c.MachineKey
   and c.MachineType = 'DiscreteLotInProgress'
  )
  OPEN csrVnameD
  FETCH NEXT FROM csrVnameD INTO @vname
  WHILE @@FETCH_STATUS = 0
  BEGIN
--1000 char = appx 40 tags
   set @taglist = @taglist + @qt+ @vname + @qt + ','
   IF LEN(@taglist) > 1000 --XP_DISCRETEHISTORYDELTA DOES NOT HANDLE INFINITE TAGS
   BEGIN
    SET @taglist = dbo.AATISfn_ClearComma(@taglist)

   SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_DiscreteHistoryDelta '
   + @qt+@st + @qt
   + ','+@qt+ @et + @qt
   + ',1000'
   + ',' + @taglist

    EXECUTE (@sSQL)
    SET @taglist = ''
   END
   FETCH NEXT FROM csrVnameD INTO @vname
--select @vname
  END
CLOSE csrVnameD
DEALLOCATE csrVnameD
IF LEN(@taglist) > 0 --GET THE REMAINING TAGS IF ANY
BEGIN
  SET @taglist = dbo.AATISfn_ClearComma(@taglist)

   SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_DiscreteHistoryDelta '
   + @qt+@st + @qt
   + ','+@qt+ @et + @qt
   + ',1000'
   + ',' + @taglist

  EXECUTE (@sSQL)
END

end try

begin catch
exec AATISsp_ErrorLog
end catch