sqlserver 存储过程 游标 双层循环

循环查询近12个月每个月不同的部门的数据

ALTER PROCEDURE [dbo].[proc存储过程名称]
    
AS
BEGIN
select distinct TeamName,teamid,OrderNo from Table1（部门） where ParentID=0 and teamid<>60 and isdel=0 order by OrderNo ;
select convert(varchar(7),dateadd(mm,number,dateadd(month,-12,getdate())),120) as dtsearch from master..spt_values where type='P' and dateadd(mm,number,dateadd(month,-12,getdate()))<=getdate();
    -- 定义错误返回信息
  declare @error int 
    -- 定义接收参数
  declare @TeamName varchar(50)
  declare @Teamid varchar(50)
  declare @OrderNo varchar(50)
  -- Step 1: 创建临时表
    CREATE TABLE #TempTable
    (
        teamid int,
        teamname VARCHAR(50),
        monthshow VARCHAR(50),
        amountall decimal(18,2),
    )
  set @error=0
  --定义游标
  declare demo_cursor cursor
  for select distinct TeamName,teamid,OrderNo from table1(部门) where ParentID=0 and teamid<>60 and isdel=0 order by OrderNo 
  
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
  --打开游标--
  open demo_cursor
  --开始循环游标变量--
  fetch next from demo_cursor into @TeamName,@Teamid,@OrderNo
  while @@FETCH_STATUS = 0  --返回被 FETCH语句执行的最后游标的状态--
  BEGIN
     --声明第二个游标
     declare d_cursor CURSOR
       for select convert(varchar(7),dateadd(mm,number,dateadd(month,-12,getdate())),120) as dtsearch from master..spt_values where type='P' and dateadd(mm,number,dateadd(month,-12,getdate()))<=getdate()
     --打开第二个游标
     OPEN d_cursor
     declare @dtsearch nvarchar(50)
       fetch next from d_cursor into @dtsearch
     --第二个由标的循环体
     WHILE @@FETCH_STATUS =0
     BEGIN
     
       ----------------
       --需要的逻辑语句--
       ----------------
      --print @TeamName+'____'+@Teamid
      --Select @TeamName as teamname,CONVERT(varchar(7),CreateTime, 120)  as monthShow,round(SUM(Amount),0) as amountall FROM table2(业绩表) where TeamId=@Teamid and CONVERT(varchar(7),CreateTime, 120)=@dtsearch Group by CONVERT(varchar(7),CreateTime, 120) order by CONVERT(varchar(7),CreateTime, 120) desc
       -- Step 2: 向临时表中插入数据
    INSERT INTO #TempTable (teamid, teamname,monthshow,amountall)
    Select @Teamid as teamid,@TeamName as teamname,CONVERT(varchar(7),CreateTime, 120)  as monthshow,round(SUM(Amount),0,1) as amountall FROM table2(业绩表)  where TeamId=@Teamid and CONVERT(varchar(7),CreateTime, 120)=@dtsearch Group by CONVERT(varchar(7),CreateTime, 120);
      set @error= @error + @@ERROR  --记录每次运行sql后是否正确，0正确
       --**--用于第二个循环体循环，缺少将成为死循环**
        fetch next from d_cursor into @dtsearch
     END
   --关闭第二个游标
   CLOSE d_cursor
   --释放第二个游标的资源
   DEALLOCATE d_cursor
   
      fetch next from demo_cursor into @TeamName,@Teamid,@OrderNo  --转到下一个游标，没有会死循环
   end
  close demo_cursor --关闭游标
  deallocate demo_cursor  --释放游标
  -- Step 3: 对临时表进行查询和操作
    SELECT * FROM #TempTable;
     -- Step 4: 销毁临时表
    DROP TABLE #TempTable;
END