CREATE TABLE tab(期号 VARCHAR(5),r1 INT,r2 INT,r3 INT,r4 INT,r5 INT,类别 VARCHAR(5))
INSERT INTO tab VALUES('0101',10,4,8,9,22,'A')
--先建表，建立完成后再建立存储过程
CREATE PROCEDURE UpdateAdd
AS
  DECLARE @r VARCHAR(100)
  SET @r=''
  DECLARE @result VARCHAR(200)
  DECLARE @qihao VARCHAR(10)
  SELECT  @qihao=(SELECT '0'+CAST((CAST(期号 AS int)+1) AS VARCHAR(4)) FROM tab WHERE r2<9 AND r3 <9)
  
  DECLARE  @i INT  
  SET  @i=1 
  WHILE @i<6 
    BEGIN  
     SET @r=@r+'r'+CAST(@i AS VARCHAR(2))++'+1'+','
     SET @i=@i+1 
    END
  SELECT @result=SUBSTRING(@r,1,LEN(@r)-1)
  -- PRINT @result
  INSERT INTO tab 
  EXEC('SELECT '+@qihao+','+@result+',类别 FROM tab WHERE r2<9 AND r3 <9')
  
  EXEC('UPDATE tab SET r2=0,r3 =0 WHERE 期号='+@qihao)
  
  EXEC UpdateAdd  --执行存储过程
  
  SELECT * FROM tab  --查询结果