Solved

How can I quit from cursor without close and deallocate it

Posted on 2006-07-18
4
645 Views
Last Modified: 2008-02-01
Hi Experts,
I have a little experience with stores, and I'm messing around with the following:
I've 2 cursors  one inside another
Outer cursor is getting the distinct Client_id from Client table, and the second cursor is using the Client_id from 1st cursor and using it to fetch the data related to that client from another table.
Now, inside 2nd cursor, if it doesn`t have data, I wish to EXIT the inner 2nd cursor and CONTINUE the processing to 1st cursor.
But I don't know how to EXIT from 2nd cursor and CONTINUE the processing of 1st cursor.

Your help will be greatly appreciated.

This is my code:


DECLARE TBL CURSOR FOR SELECT IDCLIENT FROM Client WHERE IDCLIENT in (1197,360,2049, and so on and on)
      OPEN TBL

      FETCH NEXT FROM TBL INTO @IDCLIENT
      WHILE (@@FETCH_STATUS=0)
      BEGIN
                  

      DECLARE VEST CURSOR FOR            
      SELECT   IdClient, IdEst, IdPac, Fecha FROM ResYPer
       INNER JOIN Pacientes ON ResYPer.IdClient = Pacientes.idClient

      OPEN VEST

      FETCH NEXT FROM VEST INTO  @ID_ESTUDIO, @ID_PAC, @FECHA
      WHILE (@@FETCH_STATUS=0)      
            
      BEGIN      

      IF(there isn`t records) BEGIN
               -- Here I want to exit from INNER Cursor Loop if I don't get data
               -- I don`t now how to determine if there are records or not.
               -- In the case there aren`t records it must follow with the next idClient (360)
               -- if I close and deallocate my inner cursor here, then it will throw error when it it reaches
               -- FETCH statament of inner loop and also error when it reaches CLOSE and DEALLOCATE statements for        inner loop
               -- Besides I want to know if is necesary END instruccion here
         ELSE

            -- In the case there are records it must exec the following code
            -- Besides I want to know is necesary BEGIN instruccion here, that is to say after else instruccion.
      
            IF (fnExisteResultado(@IdClient, @Id_Pac, @Id_Est) = 0)
                  BEGIN
                        INSERT INTO Control(IdTran, IdClient, IdPac, IdEst)
                        VALUES (@ID_TRAN, @IDCLIENT, @ID_PAC, @ID_EST, GETDATE())
            
                        IF @@ERROR <> 0
                        BEGIN
                              CLOSE VESTUDIOS
                              DEALLOCATE VESTUDIOS      
                              PRINT 'ERROR’                        
                        END

                  END

            -- If is necesary the BEGIN instruccion then I have to put END here?            
            FETCH NEXT FROM VEST INTO @ID_EST, @ID_PAC, @FECHA
            END
            CLOSE VEST
            DEALLOCATE VEST

      EXECUTE sp_Copia @IDCLIENT

      IF @@ERROR = 0
      BEGIN
            COMMIT TRANSACTION DATOSCLIENT
      END
      ELSE
      BEGIN
            ROLLBACK TRANSACTION DATOSCLIENT
      END

FETCH NEXT FROM TBL INTO @IDCLIENT
END
CLOSE TBL
DEALLOCATE TBL
GO
0
Comment
Question by:lcc_kaury
  • 3
4 Comments
 
LVL 7

Expert Comment

by:LandyJ
ID: 17134952
Use a BREAK statement to exit the inner most WHILE loop

0
 
LVL 7

Expert Comment

by:LandyJ
ID: 17134973
>> Besides I want to know if is necesary END instruccion here

If you have more than one statement following an IF or WHILE statement, you need to use the BEGIN & END keywords to group your statements

If @A = 12
  Begin
    SELECT ...
    UPDATE ....
  End
ELSE
  Begin
    UPdate...
    Do whatever else ...
  End
SELECT ...

If you only have one statement, Begin & End are not needed

IF @A=12
    Select ....
ELSE
    Update ...
SELECT...  <<--- This statement will be execute regardless of the value of @A

hth,
Landy
0
 
LVL 7

Accepted Solution

by:
LandyJ earned 250 total points
ID: 17135092
Here's your code, with the proper structure:

DECLARE TBL CURSOR FOR SELECT IDCLIENT FROM Client WHERE IDCLIENT in (1197,360,2049, and so on and on)
OPEN TBL

FETCH NEXT FROM TBL INTO @IDCLIENT
WHILE (@@FETCH_STATUS=0)
  BEGIN
        BEGIN TRAN DATOSCLIENT

        DECLARE VEST CURSOR FOR          
        SELECT   IdClient, IdEst, IdPac, Fecha FROM ResYPer
        INNER JOIN Pacientes ON ResYPer.IdClient = Pacientes.idClient
      
        OPEN VEST
      
        FETCH NEXT FROM VEST INTO  @ID_ESTUDIO, @ID_PAC, @FECHA
        WHILE (@@FETCH_STATUS=0)    
            
                --NO NEED for the  IF(there isn`t records) BEGIN line.  @@FETCH_STATUS will be -1 if
                -- the cursor is empty, therefore it will not execute the stements inside the WHILE structure

          Begin
                -- In the case there are records it must exec the following code
                -- Besides I want to know is necesary BEGIN instruccion here, that is to say after else instruccion.

                IF (fnExisteResultado(@IdClient, @Id_Pac, @Id_Est) = 0)
                  BEGIN
                        INSERT INTO Control(IdTran, IdClient, IdPac, IdEst)
                        VALUES (@ID_TRAN, @IDCLIENT, @ID_PAC, @ID_EST, GETDATE())

                        IF @@ERROR <> 0
                          BEGIN
                                --CLOSE VEST  -- No need to clean up VEST cursor here.  BREAK will send control to statement immed following WHILE's END
                                --DEALLOCATE VEST    
                                PRINT 'ERROR'
                                BREAK               --<<--<<---  Exits loop
                          END

                  END

                -- If is necesary the BEGIN instruccion then I have to put END here?          
                FETCH NEXT FROM VEST INTO @ID_EST, @ID_PAC, @FECHA
          End

        CLOSE VEST
        DEALLOCATE VEST
       
        EXECUTE sp_Copia @IDCLIENT
       
        IF @@ERROR = 0
          COMMIT TRANSACTION DATOSCLIENT
        ELSE
          ROLLBACK TRANSACTION DATOSCLIENT
       
        FETCH NEXT FROM TBL INTO @IDCLIENT
  END


CLOSE TBL
DEALLOCATE TBL
GO


hth,
Landy
0
 

Author Comment

by:lcc_kaury
ID: 17148853
Ok Landy, you have cleared my doubts, and the code works good.
Thank you.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Server 2008 Cluster Fail-over Errors 5 43
How to convert JSON file to csv? 7 50
Ranking Based On Value 3 28
separate column 24 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

20 Experts available now in Live!

Get 1:1 Help Now