【*存储过程*】游标+跨库+记录合并字段+异常

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

-- =============================================
-- Author:  Dotte
-- Create date: 2010-8-11
-- Description: 统计XX客户使用情况 
-- =============================================
ALTER PROCEDURE [dbo].[SP_CNIM_App_UseStatistic]
@startDate varchar(50)='1753/01/01 00:00:00',
@endDate varchar(50)='9999/12/31 23:59:59'
AS
BEGIN
--变量定义
declare @companyID nvarchar(20)       --企业号
declare @strSql nvarchar(4000)        --临时sql
declare @DataBaseName nvarchar(200)   --数据库名

--1、遍历所有的用户
--定义游标
DECLARE company_cursor CURSOR FOR
SELECT CompanyID,DataBaseName FROM PF_CompanyAttachedInfo t1 ORDER BY CompanyID
--打开游标
OPEN company_cursor
--开始遍历
FETCH NEXT FROM company_cursor INTO @CompanyID,@DataBaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    
  --企业人数
     set @strSql='UPDATE dbo.App_UseStatistic SET UserNum=( select count(1) from '+@DataBaseName+'.dbo.UserInfo where AccountState=1 ) '
  --在线人数
     set @strSql=@strSql+', OnlineNum=( select  count(1) from  (select num=count(1) from  '+@DataBaseName+'.dbo.User_LogInfo where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 group by LogUserID ) t1  ) '
     --单聊消息数
     set @strSql=@strSql+', SingleTalkMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_SingleTalkMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) '
     --群聊消息数
     set @strSql=@strSql+', GroupTalkMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_GroupTalkMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) '
     --文件消息数
     set @strSql=@strSql+', FileMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_FileMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) '
     --系统消息数
  set @strSql=@strSql+', SysMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_SysMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) ' 
     --第一次使用时间
  set @strSql=@strSql+', FirstUseDate=( select Top 1 LogTime from '+@DataBaseName+'.dbo.User_LogInfo order by LogID asc ) '   
     --最后一次使用时间
  set @strSql=@strSql+', LastUseDate=( select Top 1 LogTime from '+@DataBaseName+'.dbo.User_LogInfo order by LogID desc ) '   
     --版本
  set @strSql=@strSql+', ClientVersion=( select Top 1 Version from '+@DataBaseName+'.dbo.ClientVersion order by ID desc ) '   
    

     set @strSql=@strSql+'  WHERE companyID= '+@CompanyID   
     set @strSql=@strSql+';';

     --频道名
     set @strSql=@strSql+'
     declare @chanelNames nvarchar(1000);
     set @chanelNames='''';

     select @chanelNames=@chanelNames+chanelName+'','' from CNIM_APP.dbo.PF_Rel_ChanelCompany  a
     inner join CNIM_APP.dbo.PF_Chanel b on a.ChanelID=b.ChanelID
     where a.companyID='+@CompanyID+';

     update App_UseStatistic set ChanelName=@chanelNames where companyID='+@CompanyID
     
     --print @strSql;
     BEGIN TRY
     exec(@strSql);
      END TRY
      BEGIN CATCH
     --在这里添加错误处理语句
      END CATCH

   --移动游标
   FETCH NEXT FROM company_cursor into @CompanyID,@DataBaseName
END  --遍历结束
--关闭游标
CLOSE company_cursor
DEALLOCATE company_cursor 
 --频道数
 set @strSql='Update App_UseStatistic  set  ChanelNum=( select count(1) from CNIM_APP.dbo.PF_Rel_ChanelCompany where companyID=App_UseStatistic.companyID ) '   
 exec(@strSql);

END