title: Storing multiple rows while running a cursor in sql server
body: <p>I have written a cursor which picks up users from Users temporary table. It then finds out the latest transaction (based on many factors) and gives output messages and other results.</p>
  
  <p>I am not able to think of an idea to store these values for every user in some collection and then return in to front end. Here is my code.</p>
  
  <pre><code>declare @cur1_patientid uniqueidentifier
  declare @cur1_dueid uniqueidentifier
  declare @cur1_dueamount varchar(10)
  declare @cur1_patientname varchar(50)
  declare @cur1_paymentday varchar(2)
  declare @cur1_monthlyamount varchar(10)
  --transaction table variables start
  declare @trans_id uniqueidentifier
  declare @trans_date datetime
  declare @trans_amount float
  declare @trans_type varchar(45)
  declare @trans_status varchar(45)
  declare @trans_patVisitid uniqueidentifier
  --transaction table variables end
  
  --declare other variables that will be used - start
  declare @output_ARB_Date datetime
  declare @output_check_flag bit
  declare @output_msg varchar(MAX)
  declare @output_difference numeric(3)
  --declare other variables that will be used - end
  set @output_check_flag=0
  set @output_msg=''
  set @output_difference=0
  
  declare arb_cur cursor FAST_FORWARD for
  select PatientId,dueid,dueamount,patientname,paymentday,monthlyamount from #ARB_Report1 where patientId 
  in ('D3658295-51B5-41DF-A1A4-E34B90EBD0F7','27F37F32-4984-47CC-B470-D086192D68FB')
  
  open arb_cur
  FETCH NEXT FROM arb_cur INTO @cur1_patientid,@cur1_dueid,@cur1_dueamount,@cur1_patientname,@cur1_paymentday,@cur1_monthlyamount
  
          WHILE @@FETCH_STATUS = 0        
          BEGIN
          --SELECT @cur1_patientid,@cur1_dueid --shows patientid,dueid of cursor1
              DECLARE trans_cur CURSOR  FOR 
                  SELECT id,date,amount,type,status,patientVisitId FROM Transactions WHERE patientId=@cur1_patientid order by date desc
                  open trans_cur
                      --fetch transaction table values in local variables
                      FETCH NEXT FROM trans_cur into @trans_id,@trans_date,@trans_amount,@trans_type,@trans_status,@trans_patVisitid                  
                      --reset the variables-start
                      set @output_check_flag=0
                      set @output_msg=''
                      set @output_difference=0
                      set @output_ARB_Date=null
                      --reset the variables-end
                      WHILE (@@FETCH_STATUS = 0)
                      begin
  
                      if(@trans_type='Reoccuring' and @trans_amount=@cur1_monthlyamount and @trans_patVisitid is null)
                              begin
                                  if(@trans_status='Failed')
                                  begin
                                      --// some code
                                  end
  
                                  else if(@trans_status='Success')
                                  begin
                                      --// some code
                                  end
                          select @cur1_patientid,@cur1_monthlyamount,@trans_amount,@output_ARB_Date,@output_msg,@output_difference,@cur1_dueamount
                          ------ SOME INSERT STATEMENT HERE TO STORE THESE VALUES INTO A COLLECTION FOR EVERY PATIENT------
                          break
                      end                                             
  
                      FETCH NEXT FROM trans_cur into @trans_id,@trans_date,@trans_amount,@trans_type,@trans_status,@trans_patVisitid                      
                      end
                  CLOSE trans_cur
              DEALLOCATE trans_cur        
  FETCH NEXT FROM arb_cur INTO @cur1_patientid,@cur1_dueid,@cur1_dueamount,@cur1_patientname,@cur1_paymentday,@cur1_monthlyamount
  END 
  CLOSE arb_cur
  DEALLOCATE arb_cur
  GO
  </code></pre>