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

How can I quit from cursor without close and deallocate it

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
lcc_kaury
Asked:
lcc_kaury
  • 3
1 Solution
 
LandyJCommented:
Use a BREAK statement to exit the inner most WHILE loop

0
 
LandyJCommented:
>> 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
 
LandyJCommented:
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
 
lcc_kauryAuthor Commented:
Ok Landy, you have cleared my doubts, and the code works good.
Thank you.
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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