locked
how can i make a loop in a Stored procedure?? RRS feed

  • Question

  • Hello,

     

    i need to do the following loop:

    delete all records from  WHOLEORDER;CONSIGNMENT;BOOKTEMP TABLES.

    While exists record in booktemp table.

     

    i know like that this procedure has no sense, but it's just the first step, once it will be completed, records in BOOKTEMP will be added and deleted by procedures stored in MDB foms.

     

    The problem is that only the first line of my statements it's running :

     

    DELETE FROM WHOLEORDER          it's running                           

                                                                                                       

    DELETE FROM CONSIGNMENT          it's not running at all

    DELETE FROM BOOKTEMP               it's not running at all

     

    How can i include all of them in the select condition  EXISTS (SELECT ID FROM BOOKTEMP WHERE(ID IS NOT NULL)) ??

     

     

    Thank you very much

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

     

     

    ALTER PROCEDURE [dbo].[CUSTOMER_PRGRM]

    -- Add the parameters for the stored procedure here

    AS

    BEGIN

     

    WHILE

    EXISTS (SELECT ID FROM BOOKTEMP WHERE(ID IS NOT NULL))

    DELETE FROM WHOLEORDER

    DELETE FROM CONSIGNMENT

    DELETE FROM BOOKTEMP

     

    END

    Tuesday, July 8, 2008 7:24 AM

Answers

  • Marco,

     

    You can do it like this:

     

    Code Snippet

    IF EXISTS (SELECT ID FROM BOOKTEMP WHERE(ID IS NOT NULL))

    BEGIN

      DELETE FROM WHOLEORDER

      DELETE FROM CONSIGNMENT

      DELETE FROM BOOKTEMP

    END

     

     

    Actually, this is equivalent to a faster version (all records deleted):

     

    Code Snippet

    IF EXISTS (SELECT ID FROM BOOKTEMP WHERE(ID IS NOT NULL))

    BEGIN

      TRUNCATE TABLE WHOLEORDER

      TRUNCATE TABLE CONSIGNMENT

      TRUNCATE TABLE BOOKTEMP

    END

     

     

    Tuesday, July 8, 2008 7:41 AM

All replies

  • Marco,

     

    You can do it like this:

     

    Code Snippet

    IF EXISTS (SELECT ID FROM BOOKTEMP WHERE(ID IS NOT NULL))

    BEGIN

      DELETE FROM WHOLEORDER

      DELETE FROM CONSIGNMENT

      DELETE FROM BOOKTEMP

    END

     

     

    Actually, this is equivalent to a faster version (all records deleted):

     

    Code Snippet

    IF EXISTS (SELECT ID FROM BOOKTEMP WHERE(ID IS NOT NULL))

    BEGIN

      TRUNCATE TABLE WHOLEORDER

      TRUNCATE TABLE CONSIGNMENT

      TRUNCATE TABLE BOOKTEMP

    END

     

     

    Tuesday, July 8, 2008 7:41 AM
  • THANKS A LOT IT RUNS, AND ACTUALLY TRUNCATE IT'S A LOT FASTER THAN DELETE.

     

     

     

     

    Tuesday, July 8, 2008 7:49 AM