iteration Loop inside a cursor not working as expected

please help. The query below is not working as expected. It is expected to stop inserting when @count=1 bu开发者_如何学Got it doesn't stop. i can't figure out what the problem is , please help me out.

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

See sample data below:

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' 

The desired result should be:

@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

I do agree with Martin, it seems you don't need cursor at all. A simple join would be suffice.

Why do you want to store data in your Form Table again?