Databases
StackOverflow2013
Postsdbo
POiteration Loop inside a cursor not working as expected
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POiteration Loop inside a cursor not working as expected
    primarykey
    data
    text
    <p>please help. The query below is not working as expected. It is expected to stop inserting when @count=1 but it doesn't stop. i can't figure out what the problem is , please help me out.</p> <pre><code>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&lt;&gt;-1) begin if(@@FETCH_STATUS &lt;&gt;-2) select @count= COUNT(SerialNumb) from CARDTABLE where SerialNumb=@SerialNumb while (@count&lt;&gt;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 </code></pre> <p>See sample data below:</p> <pre><code>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' </code></pre> <p><strong>CardTable</strong> </p> <pre><code> 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' </code></pre> <p>The desired result should be:</p> <p><strong>@FormTable</strong></p> <pre><code>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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMartin Smith
    1. USjenni
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COFrom a first glance this looks like something you probably don't need a cursor for. Can you describe what it is intended to do?
      singulars
      1. POiteration Loop inside a cursor not working as expected
      1. USMartin Smith
    2. CO@Martin, it is meant to COUNT(SerialNumb) from CARDTABLE , while @count<> 1, insert into FORMTABLE but it keeps inserting over and over again.
      singulars
      1. POiteration Loop inside a cursor not working as expected
      1. USjenni
    3. COI see you've added some example data for the 2 tables. Now what is your cursor trying to do based on that data?
      singulars
      1. POiteration Loop inside a cursor not working as expected
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload