SQL存储过程伺服程序数据处理

为了处理IP回传与串口回传共用的过程，特此修改此存储过程。记录在此：

  1 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--

/* =============================================
-- Author:        Fzj
-- Create date: 20140730
-- Description:    伺服程序数据处理
--批量处理伺服数据
--处理内容说明：
--1、更新SRV表在线状态
--2、更新或新增RAWDATASTREAMS表
--3、更新或新增SRVDAYINFO表，并修改状态为在线
--参数说明：
--@SrvPhyId 设备物理码
--@RdsTime  回传时间
--@RdsBody  信息体内容
--@RdsFlag  回传类型
--修改记录
序号  修改时间    修改人    修改内容
1     20140731    FZJ       增加TRY CATCH处理，防止出现死锁导致程序阻塞
2     20140902    FZJ       将临时表RDSBACKUP内容转入临时表#tmp_rdsbackup，防止并发影响数据
3     20140912    FZJ       将COM口、TCP处理合并为一个存储过程
=============================================*/
ALTER PROCEDURE [dbo].[Proc_Srv_LotsRmDataDeal]
    @tablename varchar(50) = 'RDSBackUp'
AS
BEGIN
    SET NOCOUNT ON;
    --将传入的字符串转为数据并存入临时表中

    declare @Status nvarchar(10), @ProgramCheck nvarchar(10)
    set @Status = '在线'
    set @ProgramCheck = '转播中'

    declare @s varchar (2000), @tmpname varchar(100)
    set @tmpname = '##tmp_rds' + substring(@tablename, 3, len(@tablename) - 2)

    --判断全局临时表是否存在，如果存在则删除
    
    begin tran t1
            set @s = N'Select * into ' + @tmpname +    ' from ' + @tablename + ';' 
                + 'truncate table ' + @tablename + ';'
                + 'select * from ' + @tmpname + ';'
                --处理RDSBackUp中可能重复记录
                +  'Delete from ' + @tmpname +  
                ' Where RDS_BACKUPID not in (Select max(RDS_BACKUPID) ' 
                + ' from ' + @tmpname + ' group by SRV_PHYSICAL_CODE, RDS_FLAG);'
                --更新SRV表
                + 'Update Srv set srv_rmt_status = ''' + @Status +''' , srv_rmt_time = b.RDS_TIME
                from Srv a '
                + ' inner join ' + @tmpname + ' b  on a.Srv_physical_code = b.SRV_PHYSICAL_CODE;'

            exec (@s);

            
            
            
            if @@error <> 0 
            begin
                rollback tran t1
                return
            end
            --更新或新增RAWDATASTREAMS表
            set @s = N'Update RawDataStreams set rds_body = b.RDS_BODY, rds_time = b.RDS_TIME
                from RawDataStreams a ' +
                'inner join ' + @tmpname + ' b  on a.Srv_physical_code = b.SRV_PHYSICAL_CODE
                                and a.rds_flag = b.RDS_FLAG;'
            exec(@s)
            if @@error <> 0 
            begin
                rollback tran t1
                return
            end
            
            set @s = N'insert into RawDataStreams(rds_time, rds_body, Srv_physical_code, rds_flag)
                select RDS_TIME, RDS_BODY, SRV_PHYSICAL_CODE, RDS_FLAG ' +
                ' from ' + @tmpname + ' A
                where not exists (select 1 from RawDataStreams b where a.SRV_PHYSICAL_CODE = b.Srv_physical_code
                                and a.RDS_FLAG = b.rds_flag);'
            exec(@s)

            if @@error <> 0 
            begin
                rollback tran t1
                return
            end

            set @s = N'update SRVDayInfo set SRV_RMT_STATUS = ''' + @Status + ''' , SRV_UPDATE_DATE = b.RDS_TIME 
                from  SRVDayInfo a ' + 
                'inner join ' + @tmpname + ' b on a.SRV_PHYSICAL_CODE = b.SRV_PHYSICAL_CODE
                where  a.SRV_RMT_STATUS <> ''' + @Status +
                    ''' and Convert(varchar(50), a.SRV_REP_DATE, 112) = Convert(varchar(50), b.RDS_TIME, 112);'
            exec(@s)
            if @@error <> 0 
            begin
                rollback tran t1
                return
            end

            set @s = N'update SRVDayInfo set SRV_PROGRAM_CHECK = case b.RDS_FLAG when 1 then ''' + @ProgramCheck + ''' else '''' end
                from  SRVDayInfo a ' + 
                'inner join ' + @tmpname + ' b on a.SRV_PHYSICAL_CODE = b.SRV_PHYSICAL_CODE
                where  Convert(varchar(50), a.SRV_REP_DATE, 112) = Convert(varchar(50), b.RDS_TIME, 112);'     
            exec(@s)

            if @@error <> 0 
            begin
                rollback tran t1
                return
            end

            if (convert(varchar(20), getdate(), 114) > '00:03:00')
            begin
                set @s = N'insert into SRVDayInfo(SRV_ID, SRV_PHYSICAL_CODE, SRV_LOGICAL_CODE, SRV_ORG_CODE, SRV_ORG_ADDRESS,
                                 SRV_RMT_STATUS, SRV_REP_DATE, SRV_UPDATE_DATE, SRV_DAYINFO_REM, SRV_PROGRAM_CHECK)
                    Select A.SRV_ID, a.SRV_PHYSICAL_CODE, a.SRV_LOGICAL_CODE, a.SRV_ORG_CODEA, a.SRV_ADDRESS, ''' + @Status + ''',
                            convert(varchar(50), b.RDS_TIME, 112), b.RDS_TIME,'''', Case b.RDS_FLAG when 1 then ''' + @ProgramCheck + ''' else '''' end
                            from SRV A ' + 
                            'inner join ' + @tmpname + ' b on A.SRV_PHYSICAL_CODE = b.SRV_PHYSICAL_CODE
                            where a.SRV_RMT_SWITCH = ''启用'' and 
                                not exists (select 1 from SrvDayInfo c where c.SRV_PHYSICAL_CODE = b.SRV_PHYSICAL_CODE 
                                                and Convert(varchar(50), c.SRV_REP_DATE, 112) = Convert(varchar(50), b.RDS_TIME, 112));'
                exec(@s)
                if @@error <> 0 
                begin
                    rollback tran t1
                    return
                end

            end

            set @s = N'drop table ' + @tmpname + ';'
            exec (@s)
            
    commit tran t1
    

END