Solved

Loop through Cursors not working correctly

Posted on 2011-09-06
16
250 Views
Last Modified: 2012-05-12
Originally this cursor worked on my only 2005 server, but I am upgrading the progect and changed the behind sql code to work differently and since then I cannot get it to loop through both cursors to return all the record.

First I pull all the questions and it;s relating sql code that I need to run in the 1st cursor.  Then the secdon one opens up the sql code and runs the results and puts it all in a Temp table to work with, then it should loop to the next in the 1st cursor but it never does, or it gets stuck on the 1st line of the second currsor and keeps inserting it, or it gets lots of errors all depending on where I put the fetch and end statements.

Is there a better way to do this????


ALTER Proc [dbo].[sp_MobFilters]
as
BEGIN

declare @counter int, @countfilter int, @Sql nvarchar(4000), @PositionId int, @SSN varchar(10), @cur cursor, 
		@taskID int, @Qid int, @Posn_Data varchar(50), @Sold_Data varchar(50)
		
Declare Filter_Cursor CURSOR FOR Select	distinct mf.intQuestionId, q.strFilterSql
								From tblMobFilter mf INNER JOIN tblSRPQuestion q on 
								q.intQuestionId = mf.intQuestionId	
									
	OPEN Filter_Cursor

	FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
	 
	while @@FETCH_STATUS =0 
		BEGIN		
			--- Call the SQL Filter and assign the unit # as the variable to get at table
			set @Sql = N'set @cur = cursor for ' + @Sql  + ';open @cur';

			exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output;

			fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
			
			while @@FETCH_STATUS =0 
				BEGIN
					Insert Into tblPermTaskTemp (intQId, intPositionID, dtFound, strPosnData, strSoldData, strSSN) 
					VALUES (@Qid, @PositionId, Getdate(), @Posn_data, @Sold_Data, @SSN); 
				END
			fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
		END 
		close @cur;
		deallocate @cur	;
	
END
FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;
Close Filter_Cursor
deallocate Filter_Cursor

Open in new window

0
Comment
Question by:kdeutsch
16 Comments
 
LVL 8

Expert Comment

by:venk_r
Comment Utility
What kind of errors do you get?Can you send them along?
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
This could be down to you the second cursor reaching @@FETCH_STATUS other than zero.

What you could do is change the first while statement to an independant switch.

DECLARE @fetchswitch bit
SET @fetchswitch=0

	FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
	
	while @fetchswitch=0 
		BEGIN	

Open in new window


Then change the part which loops to the next part of the curor.

fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
IF @@FETCH_STATUS<>0 SET @fetchswitch=1

Open in new window

0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
Not sure what you mean by the "behind SQL code" but my guess would be that is where the error is.  Since it fails on the 2nd query, you might look at that to see that it runs by itself outside of this procedure.

Also, the way you've got the code written now, the both loops would loop forever because you've got your "fetch next... " outside of each loop.  So of course the @@FETCH_STATUS will not change.  So that is why when it makes it into the inner loop, it loops forever inserting the same record and never gets out.
0
 

Author Comment

by:kdeutsch
Comment Utility
All
Ok, it seems that it was failing on some of the sql I was calling into the Sp.  But what I still need is a way to get around if it pulls nothing for that procedure, i want it automatically go on tot he next


ALTER Proc [dbo].[sp_MobFilters]
as
BEGIN

declare @counter int, @countfilter int, @Sql nvarchar(4000), @PositionId int, @SSN varchar(10), @cur cursor,
            @taskID int, @Qid int, @Posn_Data varchar(50), @Sold_Data varchar(50)
            
Declare Filter_Cursor CURSOR FOR Select      distinct mf.intQuestionId, q.strFilterSql
                                                From tblMobFilter mf INNER JOIN tblSRPQuestion q on
                                                q.intQuestionId = mf.intQuestionId      
                                                      
      OPEN Filter_Cursor

      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
      
      while @@FETCH_STATUS =0
            BEGIN            
                  --- Call the SQL Filter and assign the unit # as the variable to get at table
                  set @Sql = N'set @cur = cursor for ' + @Sql  + ';open @cur';

                  exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output;

                  fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
                  
                  while @@FETCH_STATUS =0
                        BEGIN
                              Insert Into tblPermTaskTemp (intQId, intPositionID, dtFound, strPosnData, strSoldData, strSSN)
                              VALUES (@Qid, @PositionId, Getdate(), @Posn_data, @Sold_Data, @SSN);
                              
                              fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
                        END
                  close @cur;
                  deallocate @cur;
      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;
            END
      
END

Close Filter_Cursor
deallocate Filter_Cursor
      
            
0
 

Author Comment

by:kdeutsch
Comment Utility
EvilPostIt:

Ok tried this and it stops after going through 2 loops, it pulls 20 Id's with the assocaiated  sql code, so it should go through 20 times but only makes 2 rounds.

BEGIN

declare @counter int, @countfilter int, @Sql nvarchar(4000), @PositionId int, @SSN varchar(10), @cur cursor,
            @taskID int, @Qid int, @Posn_Data varchar(50), @Sold_Data varchar(50)
            
Declare Filter_Cursor CURSOR FOR Select      distinct mf.intQuestionId, q.strFilterSql
                                                From tblMobFilter mf INNER JOIN tblSRPQuestion q on
                                                q.intQuestionId = mf.intQuestionId      
DECLARE @fetchswitch bit
SET @fetchswitch=0

                                    
      OPEN Filter_Cursor

      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
      
      while @fetchswitch=0
            BEGIN            
                  --- Call the SQL Filter and assign the unit # as the variable to get at table
                  set @Sql = N'set @cur = cursor for ' + @Sql  + ';open @cur';

                  exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output;

                  fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
                  
                  while @@FETCH_STATUS =0
                        BEGIN
                              Insert Into tblPermTaskTemp (intQId, intPositionID, dtFound, strPosnData, strSoldData, strSSN)
                              VALUES (@Qid, @PositionId, Getdate(), @Posn_data, @Sold_Data, @SSN);
                              
                              fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
IF @@FETCH_STATUS<>0 SET @fetchswitch=1

                        END
                  close @cur;
                  deallocate @cur;
      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;
            END
      
END

Close Filter_Cursor
deallocate Filter_Cursor
0
 

Author Comment

by:kdeutsch
Comment Utility
All,
Ok at this point it works but when it errors out o the sq2l code the whole thing bombs out, how can I keep it going even it it bombs on a portion of that code.
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
The reason it is stopping is because you set the @FetchSwitch to 1 in the inner loop. So when it bubbles to the outer loop where that switch is checked it will stop.

In your previous comment you said,
"But what I still need is a way to get around if it pulls nothing for that procedure, i want it automatically go on tot he next"

Please be specific about to which loop and which field you are referring.
You may have to check for Null before attempting to appending it into a string.  Maybe it is failing and therefore terminating everything when you just want to skip over it.




0
 

Author Comment

by:kdeutsch
Comment Utility
dwe761:

it woud be on the inner loop, if it returns nothing instead of doing the insert statment it should just go to teh next outer loop.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 10

Expert Comment

by:dwe761
Comment Utility
I'm going to offer a number of ideas some of which may be worth pursuing and others may be totally off because I don't know your data or your structures.

First of all, is tblPermTaskTemp set up to allow NULLs in all of the fields into which you are inserting data?   intPositionID, dtFound, strPosnData, strSoldData, strSSN.
If not, then you may have to change that table to allow nulls.

Also, we have to assume that @PositionId has a value since it's probably the key and otherwise, you would've gotten @@FETCH_STATUS other than zero.

You could insert "IF" statements prior to your INSERT statements, but that may be putting a bandaid on a problem that could be prevented.  

Our problem in helping you on this is not having the data or knowing what you changed from when this procedure used to work.
You say that this procedure worked before you changed the "behind sql code".  Does that mean you changed the values in field strFilterSql in table  tblSRPQuestion?  If so, the way I would debug this would be to backup this table and then remove all rows but one.  Then run your proc on it.  If you get the expected results, then add the next row into it from your backed up table.

Keep working this way until you run into the row that is causing the problem.
Maybe that will give you a hint on what you need to fix.

0
 

Author Comment

by:kdeutsch
Comment Utility
All,
Ok, the Sp is running and returning all teh field that I and looking for in teh main and sub loops.  this is example data from the main Cursor.

intQuestionId                 strSQL
    151                            Select p.intPositionId, p.strSCTYClear ,s.sidstrSCTY_CLNC, pp.strssn from tblMobUnitPosition as p INNER JOIN tblMobUnitPersonnel as pp on pp.intPositionId = p.intPositionId INNER JOIN cms.dbo.tblsidpers as s on s.sidstrSSN_SM = pp.strssn where (Case When (s.sidstrSCTY_CLNC = 'M' or s.sidstrSCTY_CLNC = 'N' or s.sidstrSCTY_CLNC = 'P') then 'BAD' When (ascii(s.sidstrSCTY_CLNC) < ascii(p.strSCTYClear)+2) then 'GOOD' else 'BAD' End) = 'BAD' And p.intUnitMobId IN (Select intUnitMobID from tblMobFilter)

So the inner loop then is made to run this sql and any data it finds it puts it in tblPermTaskTemp.  I only pull 4 pieces of data in teh sql quesry and intQId I get from above.

Inner Loop Data
intPositionId             POSN_Data              Sold_Data                strSSN
  221                              G                               F                      000000000

What I would like to do is if the inner quesry returns no rows to insert into tblpermtaskemp, I would like to bypass it insead of it having run this portion.
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
Please post your current version of your procedure because if this statement:

                  fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;

is run prior to the inner loop which checks @@FETCH_STATUS=0, I don't see why it would enter into that inner loop if no records were returned.
0
 

Author Comment

by:kdeutsch
Comment Utility
ALTER Proc [dbo].[sp_MobFilters]
as
BEGIN

declare @counter int, @countfilter int, @Sql nvarchar(4000), @PositionId int, @SSN varchar(10), @cur cursor,
            @taskID int, @Qid int, @Posn_Data varchar(50), @Sold_Data varchar(50)
            
Declare Filter_Cursor CURSOR FOR Select      distinct mf.intQuestionId, q.strFilterSql
                                                From tblMobFilter mf INNER JOIN tblSRPQuestion q on
                                                q.intQuestionId = mf.intQuestionId      
                                                      
      OPEN Filter_Cursor

      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
      
      while @@FETCH_STATUS =0
            BEGIN            
                  --- Call the SQL Filter and assign the unit # as the variable to get at table
                  set @Sql = N'set @cur = cursor for ' + @Sql  + ';open @cur';

                  exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output;

                  fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
                  
                  while @@FETCH_STATUS =0
                        BEGIN
                              Insert Into tblPermTaskTemp (intQId, intPositionID, dtFound, strPosnData, strSoldData, strSSN)
                              VALUES (@Qid, @PositionId, Getdate(), @Posn_data, @Sold_Data, @SSN);
                              
                              fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
                        END
                  close @cur;
                  deallocate @cur;
      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;
            END
      
END

Close Filter_Cursor
deallocate Filter_Cursor
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
Comment Utility
Sorry, I got bogged down with my other work and can now get back to this.

As in my previous post, I'm still puzzled:
If this statement:

 
fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;

Open in new window


is producing no output, @@FETCH_STATUS should be non-zero.  Therefore, the current logic should prevent it from going into the inner loop.  And you're saying that it does?

Please explain exactly what happens when that query produces no value for @cur.

You could try putting a PRINT statement prior to the Inner loop to see what the values are of @PositionId, @Posn_Data, @Sold_Data, and @SSN.





0
 

Author Comment

by:kdeutsch
Comment Utility
dwe761:,

Ok, I am good, I do not do these very often maybe 7 times over a year so i never get real practice in waht I am doing, It seems to work except for when the sql code inside it breaks.  Would there be a way to return the intQuestionis to an error code so that if it does break I will know which specific code its breaking on?
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
OK.  
Here is a simple example of how to trap a bad SQL statement when using dynamic SQL:

 
declare @sql nvarchar(1000)
set @sql = N'select * from ThisTblDoesNotExist'

BEGIN TRY
 exec sp_executesql @Sql
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber, @sql As BadSQLStatement
END CATCH;

Open in new window


So in your case you could do something like this to collect a list of QID's that relate to invalid SQL statements:

 
ALTER Proc [dbo].[sp_MobFilters]
as
BEGIN

declare @counter int, @countfilter int, @Sql nvarchar(4000), @PositionId int, @SSN varchar(10), @cur cursor,
            @taskID int, @Qid int, @Posn_Data varchar(50), @Sold_Data varchar(50)
declare @Errors nvarchar(1000)    -- To save list of bad queries 
Declare Filter_Cursor CURSOR FOR Select      distinct mf.intQuestionId, q.strFilterSql
                                                From tblMobFilter mf INNER JOIN tblSRPQuestion q on
                                                q.intQuestionId = mf.intQuestionId      
                   
		SET @Errors = ''                                   
      OPEN Filter_Cursor

      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
      
      while @@FETCH_STATUS =0
            BEGIN            
                  --- Call the SQL Filter and assign the unit # as the variable to get at table
                  set @Sql = N'set @cur = cursor for ' + @Sql  + ';open @cur';

						BEGIN TRY
							exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output;
						END Try
						BEGIN CATCH
							SET @Errors = @Errors + CASE WHEN len(@Errors)>0 THEN ',' ELSE '' END + cast(@QId as nvarchar)
						END CATCH;

                  fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
                  
                  while @@FETCH_STATUS =0
                        BEGIN
                              Insert Into tblPermTaskTemp (intQId, intPositionID, dtFound, strPosnData, strSoldData, strSSN)
                              VALUES (@Qid, @PositionId, Getdate(), @Posn_data, @Sold_Data, @SSN);
                              
                              fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
                        END
                  close @cur;
                  deallocate @cur;
      FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;
            END
      
END

Close Filter_Cursor
deallocate Filter_Cursor

Open in new window


Hope this helps.
0
 

Author Closing Comment

by:kdeutsch
Comment Utility
Thanks it works
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now