游标内的迭代循环未按预期工作

Question

请帮忙。下面的查询未按预期工作。当 @count=1 时预计会停止插入，但它不会停止。我不知道问题出在哪里，请帮我解决。

create procedure mypro
as

declare @count int
declare aa cursor

for select SerialNumb from FORMTABLE

  open aa

      declare @SerialNumb varchar(11)
      fetch next from aa into @SerialNumb

      while(@@FETCH_STATUS<>-1)
       begin
          if(@@FETCH_STATUS <>-2)
          select @count= COUNT(SerialNumb) from CARDTABLE where SerialNumb=@SerialNumb
          while (@count<>1)
           begin

     ---- variables declaration

          declare @name
          declare @location
          declare @status


         select  @name = name, @location = location, @status=status

         from FORMTABLE  where SerialNumb = @SerialNumb


        insert into FORMTABLE values(@name, @location, @status)


        set @count = @count-1
      end
        fetch next from aa into @SerialNumb
          end

close aa

deallocate aa

请参阅下面的示例数据：

DECLARE @FormTable TABLE
(
Serialnumb INT,
[GROUP] CHAR(1),
CLASS VARCHAR(50) 
)

INSERT INTO @FormTable
SELECT 400,'A','Science' UNION ALL 
SELECT 401,'B','Social science' UNION ALL 
SELECT 402,'C','philosophy' UNION ALL 
SELECT 403,'D','Engineering' 

CardTable

 DECLARE @CardTable TABLE
(
Id INT PRIMARY KEY,
Serialnumb INT,
Name VARCHAR(50) 
)

INSERT INTO @CardTable
SELECT 1,400,'Charey' UNION ALL 
SELECT 2,400,'Mike' UNION ALL 
SELECT 3,400,'James' UNION ALL 
SELECT 4,401,'Vina' UNION ALL 
SELECT 5,400,'Gloria' UNION ALL 
SELECT 6,401,'Faith' UNION ALL 
SELECT 7,401,'Sarah' UNION ALL 
SELECT 8,402,'Joy' UNION ALL 
SELECT 9,402,'Rita' 

所需的结果应该是：

@FormTable

Serialno    Group       Class
400     A       Science
401     B       Social science
402     C       philosophy
403     D       Engineering
400     A       Science
400     A       Science
400     A       Science
401     B       Social science
401     B       Social science
402     C       philosophy

Answer 1

我确实同意马丁的观点，看来你根本不需要光标。一个简单的连接就足够了。

为什么要再次在表单中存储数据？