Solved

Loop through Cursors not working correctly

Posted on 2011-09-06
16
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 8

Expert Comment

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

Expert Comment

by:EvilPostIt
ID: 36488628
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
ID: 36488635
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:kdeutsch
ID: 36489151
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
ID: 36489265
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
ID: 36489272
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
ID: 36489572
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
ID: 36489688
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
 
LVL 10

Expert Comment

by:dwe761
ID: 36489972
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
ID: 36490135
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
ID: 36490844
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
ID: 36491054
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
ID: 36493404
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
ID: 36495054
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
ID: 36495767
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
ID: 36545046
Thanks it works
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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