存储过程使用表变量或临时表代替游标Fetch实例，访问远程数据库

if object_id(‘tempdb..#Tmp‘) is not null 2 Begin 3 drop table #Tmp 4 End 5 6 create table #Tmp --创建临时表#Tmp为获取远程设备信息使用 7 ( 8 deviceId varchar(50), 9 deviceNo varchar(200), 10 FlagID TINYINT 11 ); 12 declare @i int,--执行一条sql语句的临时变量,用于远程数据库配置信息循环 13 @j int,--执行一条sql语句的临时变量，用于更新设备信息名称 14 @user varchar(50),--用户名 15 @password varchar(100),--密码 16 @ssid varchar(100),--ssid 17 @db varchar(100),--数据库名称 18 @server varchar(100),--远程数据库server 19 @database varchar(200), 20 @remoteid int 21 --定义远程数据库配置信息临时表 22 declare @tRemoteOffice table 23 ( 24 RemoteId int, 25 RomoteUser varchar(50), 26 RemotePassword varchar(100), 27 RemoteSsid varchar(100), 28 RemoteDb varchar(100), 29 RemoteName varchar(100), 30 FlagID TINYINT 31 ) 32 33 begin 34 35 36 BEGIN TRY---------------------开始捕捉异常 37 38 39 --1.查询远程数据库配置表，插入查询数据到临时表 40 insert @tRemoteOffice select remote_id, remote_user,remote_psw,remote_ssid,remote_database,remote_name,0 from dbo.RemoteDBInfo; 41 print CONVERT(varchar(100), GETDATE(),21)+‘插入远程数据库配置信息到临时表，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 42 43 set @i=1; 44 --Return_connectError: 45 while(@i>=1) 46 begin 47 Return_begin: 48 --2.选取临时表里的一条数据 49 SELECT TOP 1 @remoteid=RemoteId, @user = RomoteUser,@password=RemotePassword,@ssid=RemoteSsid,@db=RemoteDb,@server=RemoteName FROM @tRemoteOffice WHERE FlagID=0; 50 SET @i=@@ROWCOUNT 51 IF @i<=0 GOTO Return_Lab 52 print ‘开始连接远程数据库,ssid:‘+@ssid; 53 begin try 54 --3.连接远程数据库 55 exec sp_addlinkedserver @server, ‘ ‘, ‘SQLOLEDB‘, @ssid ; 56 exec sp_addlinkedsrvlogin @server, ‘false ‘,null, @user, @password ; 57 58 select @database=@server+‘.‘+@db; 59 --4.查询远程数据库的机构信息 60 declare @officeID nvarchar(50), 61 @officeName nvarchar(200) 62 63 declare @searchRemoteOfficeSql Nvarchar(max)=N‘SELECT @Id=office_id,@Name=office_name FROM ‘+@database+‘.dbo.Office where caste=0‘; 64 Exec sp_executesql @searchRemoteOfficeSql,N‘@Id nvarchar(50) output,@Name nvarchar(100) output‘,@officeID output,@officeName output; 65 66 print CONVERT(varchar(100), GETDATE(),21)+‘查询远程数据库机构信息，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 67 update dbo.RemoteDBInfo set remote_type=1 WHERE remote_ssid = @ssid ; 68 end try 69 begin catch 70 print ‘连接远程数据库‘+@ssid+‘异常‘ 71 exec sp_dropserver @server, ‘droplogins ‘; 72 UPDATE @tRemoteOffice SET FlagID=1 WHERE RemoteSsid = @ssid; 73 update dbo.RemoteDBInfo set remote_type=0 WHERE remote_ssid = @ssid ; 74 goto Return_begin 75 end catch 76 --5.查询已连接的远程数据库officeid是否已在本地表中 77 declare @SearchLocalUnitySql Nvarchar(max)=N‘select UnityId from dbo.Unity where UnityId =@Id ‘; 78 Exec sp_executesql @SearchLocalUnitySql,N‘@Id nvarchar(50)‘,@Id=@officeID; 79 80 --6.不在本地表中，插入新的机构信息 81 if @@ROWCOUNT=0 82 begin 83 84 declare @InsertUnityOfficeSql Nvarchar(max)=N‘insert into dbo.Unity (UnityId,Name,ParentId,Type,remote_id) values(@id,@name,NULL,0,@remote_id) ‘; 85 Exec sp_executesql @InsertUnityOfficeSql,N‘@id nvarchar(50),@name nvarchar(200),@remote_id int‘,@id=@officeID,@name=@officeName,@remote_id=@remoteid; 86 print CONVERT(varchar(100), GETDATE(),21)+‘插入新的机构信息，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 87 end 88 --7.在本地表中，更新机构名称 89 else 90 begin 91 declare @UpdateUnityOfficeNameSql Nvarchar(max)=N‘update dbo.Unity set Name=@Name1,remote_id=@remote_id where UnityId=@Id1 ‘; 92 Exec sp_executesql @UpdateUnityOfficeNameSql,N‘@Name1 nvarchar(100),@Id1 nvarchar(50),@remote_id int‘,@Name1=@officeName,@Id1=@officeID,@remote_id=@remoteid; 93 print CONVERT(varchar(100), GETDATE(),21)+‘更新机构信息，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 94 end 95 96 97 98 --8.插入设备信息到临时表 99 truncate table #Tmp 100 declare @deviceID varchar(50), 101 @deviceName varchar(200) 102 103 declare @InsertRemoteDeviceSql Nvarchar(max)=N‘insert #Tmp select device_id,device_no,0 FROM ‘+@server+‘.‘+@db+‘.dbo.device where parent_id is null and is_bom=0 and status=1 ‘; 104 Exec sp_executesql @InsertRemoteDeviceSql; 105 print CONVERT(varchar(100), GETDATE(),21)+‘插入设备信息到临时表，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 106 set @j=1; 107 while(@j>=1) 108 begin 109 --9.选取临时表里的一条数据 110 SELECT TOP 1 @deviceID = deviceId,@deviceName=deviceNo FROM #Tmp WHERE FlagID=0; 111 SET @j=@@ROWCOUNT 112 IF @j<=0 GOTO Return_device 113 --10.查询临时表里的deviceId是否在Unity里 114 declare @SearchLocalUnityDeviceSql Nvarchar(max)=N‘select UnityId from dbo.Unity where UnityId =@id ‘; 115 Exec sp_executesql @SearchLocalUnityDeviceSql,N‘@id nvarchar(50)‘,@id=@deviceID; 116 --@@ROWCOUNT被执行一次后清零 117 --print CONVERT(varchar(100), GETDATE(),21)+‘查询临时表里的设备id是否在Unity里，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 118 --11.不在表数据里，插入新的设备信息 119 if @@ROWCOUNT=0 120 begin 121 122 declare @InsertUnityDeviceSql Nvarchar(max)=N‘insert into dbo.Unity (UnityId,Name,ParentId,Type) values (@id,@name,@parentid,1) ‘; 123 Exec sp_executesql @InsertUnityDeviceSql,N‘@id nvarchar(50),@name nvarchar(200),@parentid nvarchar(50)‘,@id=@deviceID,@name=@deviceName,@parentid=@officeID; 124 print CONVERT(varchar(100), GETDATE(),21)+‘插入新的设备信息，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 125 end 126 --12.在表数据里，更新设备名称 127 else 128 begin 129 declare @UpdateUnityDeviceNameSql Nvarchar(max)=N‘update dbo.Unity set Name=@name where UnityId=@id ‘; 130 Exec sp_executesql @UpdateUnityDeviceNameSql,N‘@name nvarchar(200),@id nvarchar(50)‘,@name=@deviceName,@id=@deviceID; 131 print CONVERT(varchar(100), GETDATE(),21)+‘更新设备名称，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 132 end 133 134 --13.获取本地FireReport表的最新数据时间 135 declare @fireReportTime DateTime 136 declare @SearchFireReportTime Nvarchar(max)=N‘SELECT top 1 @time=FindTime FROM dbo.FireReport where UnityId=@id order by FindTime desc‘; 137 Exec sp_executesql @SearchFireReportTime,N‘@time Datetime output,@id nvarchar(50)‘,@fireReportTime output,@id=@deviceID; 138 --14.插入火情信息数据 139 if @fireReportTime=null 140 begin 141 set @fireReportTime=GETDATE(); 142 end 143 144 --0 未处理，1确认，2误报，3取消，4上报，5未知状态 145 declare @InsertFireSql Nvarchar(max)=N‘insert into dbo.FireReport(FindTime,FireType,UnityId) 146 select raised_dt 147 ,CASE WHEN confirmed = 0 and cancelled=0 and discarded=0 and reported=0 THEN 0 148 WHEN confirmed = 1 and cancelled=0 and discarded=0 and reported=0 THEN 1 149 WHEN confirmed = 0 and cancelled=0 and discarded=1 and reported=0 THEN 2 150 WHEN confirmed = 0 and cancelled=3 and discarded=0 and reported=0 THEN 3 151 WHEN confirmed = 0 and cancelled=0 and discarded=0 and reported=4 THEN 4 152 ELSE 5 END,@id FROM ‘+@server+‘.‘+@db+‘.dbo.fire where raised_dt>@time and tower_id=@id order by raised_dt desc‘; 153 Exec sp_executesql @InsertFireSql,N‘@id nvarchar(50),@time DateTime‘,@id=@deviceID,@time=@fireReportTime; 154 print CONVERT(varchar(100), GETDATE(),21)+‘.插入火情信息数据，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘ 155 156 157 --15.插入设备状态数据 158 --首先判断在视图中是否存在设备运行状态 159 declare @SearchViewSql Nvarchar(max)=N‘SELECT tower_id FROM ‘+@server+‘.‘+@db+‘.dbo.vw_cruise_state where tower_id=@id ‘; 160 Exec sp_executesql @SearchViewSql,N‘@id nvarchar(50)‘,@id=@deviceID; 161 if @@ROWCOUNT>0 162 begin 163 declare @InsertDeviceStatusSql Nvarchar(max)=N‘Insert into dbo.DeviceStatus(UnityID,GetTime,Status) SELECT tower_id,GETDATE(),aa_status FROM ‘+@server+‘.‘+@db+‘.dbo.vw_cruise_state where tower_id=@id ‘; 164 Exec sp_executesql @InsertDeviceStatusSql,N‘@id nvarchar(50)‘,@id=@deviceID; 165 print CONVERT(varchar(100), GETDATE(),21)+‘插入设备运行状态数据，一共执行‘+convert(varchar(5),@@ROWCOUNT)+‘条‘; 166 print CONVERT(varchar(100), GETDATE(),21)+‘设备ID为：‘+@deviceID; 167 end 168 169 IF @@error=0 170 UPDATE #Tmp SET FlagID=1 WHERE deviceId = @deviceID 171 172 Return_device: 173 end 174 truncate table #Tmp; 175 176 exec sp_dropserver @server, ‘droplogins ‘; 177 IF @@error=0 178 UPDATE @tRemoteOffice SET FlagID=1 WHERE RemoteSsid = @ssid 179 180 Return_Lab: 181 end 182 DROP TABLE [dbo].#Tmp 183 184 END TRY-----------结束捕捉异常 185 186 BEGIN CATCH------------有异常被捕获 187 print @@error; 188 --IF @@TRANCOUNT > 0---------------判断有没有事务 189 --BEGIN 190 -- ROLLBACK TRAN----------回滚事务 191 --END 192 print ERROR_MESSAGE(); 193 UPDATE @tRemoteOffice SET FlagID=1 WHERE RemoteSsid = @ssid; 194 exec sp_dropserver @server, ‘droplogins ‘; -----------执行存储过程将错误信息记录在表当中 195 END CATCH--------结束异常处理 196 197 end

 

存储过程使用表变量或临时表代替游标Fetch实例，访问远程数据库

标签：