• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Loop through Cursors not working correctly

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
kdeutsch
Asked:
kdeutsch
1 Solution
 
venk_rCommented:
What kind of errors do you get?Can you send them along?
0
 
EvilPostItCommented:
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
 
dwe761Software EngineerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
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
 
dwe761Software EngineerCommented:
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
 
kdeutschAuthor Commented:
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
 
dwe761Software EngineerCommented:
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
 
kdeutschAuthor Commented:
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
 
dwe761Software EngineerCommented:
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
 
kdeutschAuthor Commented:
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
 
dwe761Software EngineerCommented:
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
 
kdeutschAuthor Commented:
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
 
dwe761Software EngineerCommented:
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
 
kdeutschAuthor Commented:
Thanks it works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now