Solved

How can I quit from cursor without close and deallocate it

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query and VBA 5 46
sql server query 18 41
interpreting data from function COLUMNS_UPDATED 2 18
DataType for bit Comparison 11 18
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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