这几天写的存储过程

SET ANSI_NULLS  ON GO SET QUOTED_IDENTIFIER  ON GO ALTER  procedure  [ dbo ]. [ Insert_TSM_Messages ] as begin  declare  @ids  int; set  @ids = 1;      declare  @PlotID  nvarchar( 50); declare   @SendingTime  datetime; declare   @AlarmID  int; declare  @state  int;  declare  @RegionName  nvarchar( 50);     -- 创建读取数据的游标     declare  API_CEODasktop  cursor     -- 读取数据     for  select   [ RegionName ]  from  [ dbo ]. [ SysCycle ]    group  by   [ RegionName ]      open API_CEODasktop  -- 打开游标     fetch  next  from API_CEODasktop  into  @RegionName          while( @@FETCH_STATUS = 0)     begin           if( @ids > 6)            set  @ids = 1;           while  @ids <= 6           begin                  declare  @states  int;              declare  @RegionNames  nvarchar( 50);              declare  @PlotIDs  int;              declare  @SendingTimes  datetime;              declare  @RegionID  int; -- 区域ID              declare  @BU_RegionID  int; -- 事业部id              declare  @事业部count  int;              declare  @区域count  int;              declare  @事业部insert  int;              set  @事业部insert = 0;              declare  @区域insert  int;              set  @区域insert = 0;              declare   @NameClass  nvarchar( 50);               if( @ids = 1)               begin                set  @NameClass = ' 个地块计划意向协议时间即将到期 ';               end               if( @ids = 2)                set  @NameClass = ' 个地块计划正式协议时间即将到期 ';               if( @ids = 3)                 set  @NameClass = ' 个地块计划完成确权时间即将到期 ';               if( @ids = 4)                 set  @NameClass = ' 个地块计划意向协议时间已超时 ';               if( @ids = 5)                 set  @NameClass = ' 个地块计划正式协议时间已超时 ';               if( @ids = 6)                 set  @NameClass = ' 个地块计划完成确权时间已超时 ';              if( @ids >= 1  and  @ids <= 3)              begin              declare select_SysCycle  cursor              for  select PlotID,SendingTime,  [ state ], [ RegionName ]  from  [ dbo ]. [ SysCycle ]   where  [ state ] = @ids  and RegionName = @RegionName  and SendingTime > GETDATE()              open select_SysCycle              fetch  next  from select_SysCycle  into  @PlotIDs, @SendingTimes,  @states, @RegionNames                           while( @@FETCH_STATUS = 0)              begin              -- 查询出事业部id和区域id                 select  @RegionID =RegionID,  @BU_RegionID =BU_RegionID  from  RegionList   where  RegionID =( select RegionID  from   [ dbo ]. [ ProjectList ]   where  PlatFormID =( select  [ PlatFormID ]  from  [ dbo ]. [ Pro_PlotInfo ]  where  [ PlotID ] = @PlotIDs))                 select  @事业部count = count( *)  from  [ dbo ]. [ AlarmReceivers ]  where OrgPrimarykey = @BU_RegionID  and PresidentType = 1                 select  @区域count = count( *)  from  [ dbo ]. [ AlarmReceivers ]  where OrgPrimarykey = @RegionID  and PresidentType = 2                 if( @事业部count > 0)                 begin                   set  @事业部insert = @事业部insert + 1;                 end                 if( @区域count > 0)                 begin                   set  @区域insert = @区域insert + 1;                 end              fetch  next  from select_SysCycle  into  @PlotIDs, @SendingTimes,  @states, @RegionNames  -- -循环结束              end              -- 关闭内层游标              close select_SysCycle              deallocate select_SysCycle              end              if( @ids >= 4  and  @ids <= 6)              begin              declare select_Sys  cursor              for  select PlotID,SendingTime,  [ state ], [ RegionName ]  from  [ dbo ]. [ SysCycle ]   where  [ state ] = @ids  and RegionName = @RegionName  and SendingTime > GETDATE()              open select_Sys              fetch  next  from select_Sys  into  @PlotIDs, @SendingTimes,  @states, @RegionNames                           while( @@FETCH_STATUS = 0)              begin              -- 查询出事业部id和区域id                 select  @RegionID =RegionID,  @BU_RegionID =BU_RegionID  from  RegionList   where  RegionID =( select RegionID  from   [ dbo ]. [ ProjectList ]   where  PlatFormID =( select  [ PlatFormID ]  from  [ dbo ]. [ Pro_PlotInfo ]  where  [ PlotID ] = @PlotIDs))                 select  @事业部count = count( *)  from  [ dbo ]. [ AlarmReceivers ]  where OrgPrimarykey = @BU_RegionID  and PresidentType = 1                 select  @区域count = count( *)  from  [ dbo ]. [ AlarmReceivers ]  where OrgPrimarykey = @RegionID  and PresidentType = 2                 if( @事业部count > 0)                 begin                   set  @事业部insert = @事业部insert + 1;                 end                 if( @区域count > 0)                 begin                   set  @区域insert = @区域insert + 1;                 end              fetch  next  from select_Sys  into  @PlotIDs, @SendingTimes,  @states, @RegionNames  -- -循环结束              end              -- 关闭内层游标              close select_Sys              deallocate select_Sys              end          -- --------------------------插入数据开始----------------------------------------              if( @事业部insert > 0)            begin              declare  @LoginName  nvarchar( 50);              declare insert_TSM_Messs  cursor              for  select LoginName  from  [ dbo ]. [ AlarmReceivers ]  where OrgPrimarykey = @BU_RegionID  and PresidentType = 1              open insert_TSM_Messs              fetch  next  from insert_TSM_Messs  into  @LoginName              while( @@FETCH_STATUS = 0)              begin                declare  @HX_CELL_PHONE  nvarchar( 50); -- 手机号码                declare  @HX_BUSN_PHONE  nvarchar( 50); --               -- --------------------------ftx账号插入-----------------------------                   INSERT  INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)                   values( NEWID(),                  @LoginName,                 ( @RegionName + ' 消息提醒 '),                  (( select  cast( @RegionName  as   nvarchar( 50))) +( select  cast( @事业部insert  as  nvarchar( 5))) + @NameClass), 1, 1, GETDATE(),( select  CONVERT( VARCHAR, GETDATE() + 1, 23) + '  08:30:00 '), getdate(), 0, 1, '')                     -- --------------------------ftx账号插入结束-----------------------------                  -- --------------------手机短信发送开始-----------------------------                  select  @HX_BUSN_PHONE =HX_BUSN_PHONE,  @HX_CELL_PHONE =HX_CELL_PHONE  from  ds.PERS_ALL  where HX_OPRID = @LoginName                  if( LEN( @HX_CELL_PHONE) >= 11  and  LEN( @HX_CELL_PHONE) < 12)                   begin                                   INSERT  INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)                   values( NEWID(),                  @HX_CELL_PHONE,                 ( @RegionName + ' 消息提醒 '),                  (( select  cast( @RegionName  as   nvarchar( 50))) +( select  cast( @事业部insert  as  nvarchar( 5))) + @NameClass), 1, 2, GETDATE(),( select  CONVERT( VARCHAR, GETDATE() + 1, 23) + '  08:30:00 '), getdate(), 0, 1, '')                     end                   else                   begin                   INSERT  INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)                   values( NEWID(),                  @HX_BUSN_PHONE,                 ( @RegionName + ' 消息提醒 '),                   (( select  cast( @RegionName  as   nvarchar( 50))) +( select  cast( @事业部insert  as  nvarchar( 5))) + @NameClass), 1, 2, GETDATE(),( select  CONVERT( VARCHAR, GETDATE() + 1, 23) + '  08:30:00 '), getdate(), 0, 1, '')                     end                   -- --------------------手机短信发送结束-----------------------------              fetch  next  from insert_TSM_Messs  into  @LoginName              end              close insert_TSM_Messs              deallocate insert_TSM_Messs            end            if( @事业部insert > 0)            begin              declare  @LoginNames  nvarchar( 50);              declare insert_TSM  cursor              for  select LoginName  from  [ dbo ]. [ AlarmReceivers ]  where OrgPrimarykey = @RegionID  and PresidentType = 2              open insert_TSM              fetch  next  from insert_TSM  into  @LoginNames              while( @@FETCH_STATUS = 0)              begin                declare  @HX_CELL_PHONEs  nvarchar( 50); -- 手机号码                declare  @HX_BUSN_PHONEs  nvarchar( 50); --               -- --------------------------ftx账号插入-----------------------------                   INSERT  INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)                   values( NEWID(),                  @LoginName,                 ( @RegionName + ' 消息提醒 '),                  (( select  cast( @RegionName  as   nvarchar( 50))) +( select  cast( @事业部insert  as  nvarchar( 5))) + @NameClass), 1, 1, GETDATE(),( select  CONVERT( VARCHAR, GETDATE() + 1, 23) + '  08:30:00 '), getdate(), 0, 1, '')                     -- --------------------------ftx账号插入结束-----------------------------                  -- --------------------手机短信发送开始-----------------------------                  select  @HX_BUSN_PHONE =HX_BUSN_PHONE,  @HX_CELL_PHONE =HX_CELL_PHONE  from  ds.PERS_ALL  where HX_OPRID = @LoginNames                  if( LEN( @HX_CELL_PHONEs) >= 11  and  LEN( @HX_CELL_PHONEs) < 12)                   begin                                   INSERT  INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)                   values( NEWID(),                  @HX_CELL_PHONEs,                 ( @RegionName + ' 消息提醒 '),                  (( select  cast( @RegionName  as   nvarchar( 50))) +( select  cast( @事业部insert  as  nvarchar( 5))) + @NameClass), 1, 2, GETDATE(),( select  CONVERT( VARCHAR, GETDATE() + 1, 23) + '  08:30:00 '), getdate(), 0, 1, '')                     end                   else                   begin                   INSERT  INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)                   values( NEWID(),                  @HX_BUSN_PHONEs,                 ( @RegionName + ' 消息提醒 '),                   (( select  cast( @RegionName  as   nvarchar( 50))) +( select  cast( @事业部insert  as  nvarchar( 5))) + @NameClass), 1, 2, GETDATE(),( select  CONVERT( VARCHAR, GETDATE() + 1, 23) + '  08:30:00 '), getdate(), 0, 1, '')                     end                   -- --------------------手机短信发送结束-----------------------------              fetch  next  from insert_TSM  into  @LoginNames              end              close insert_TSM              deallocate insert_TSM            end            -- ------------------------------插入数据结束----------------------------------------------            set  @ids = @ids + 1;                    end -- 第二层循环结束      -- 第一层循环结束      fetch  next  from API_CEODasktop  into  @RegionName     end     -- 关闭游标     close API_CEODasktop     -- 删除游标     deallocate API_CEODasktop end

 

 

 

转载于:https://www.cnblogs.com/btmc/p/5535212.html