Solved

How can I quit from cursor without close and deallocate it

Posted on 2006-07-18
4
655 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 42
Inserting oldest record into new table. 5 25
Help in Bulk Insert 9 35
SQL Server Import/Error Wizard error 12 19
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

831 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