?
Solved

How can I quit from cursor without close and deallocate it

Posted on 2006-07-18
4
Medium Priority
?
694 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
[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
  • 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 1000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

649 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