/************************************************************

 * Code formatted by setyg

 * Time: 2014/7/29 10:04:44

 ************************************************************/

 

CREATE PROC HandleEmailRepeat

AS

DECLARE email CURSOR

FOR

  SELECT e.email

     ,e.OrderNo

     ,e.TrackingNo

  FROM  Email20140725 AS e

  WHERE e.[status] = 0

  ORDER BY

      e.email

     ,e.OrderNo

     ,e.TrackingNo

 

BEGIN

  DECLARE @@email       VARCHAR(200)

      ,@firstEmail     VARCHAR(200)

      ,@FirstOrderNO    VARCHAR(300)

      ,@FirstTrackingNO   VARCHAR(300)

      ,@NextEmail      VARCHAR(200)

      ,@@orderNO      VARCHAR(300)

      ,@NextOrderNO     VARCHAR(50)

      ,@@trackingNO     VARCHAR(300)

      ,@NextTrackingNO   VARCHAR(50)

   

  BEGIN

    OPEN email;

    FETCH NEXT FROM email INTO @firstEmail,@FirstOrderNO, @FirstTrackingNO;

    FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO;

    IF @NextEmail!=@firstEmail

    BEGIN

      INSERT INTO Email20140725Test

       (

        email

        ,OrderNo

        ,TrackingNo

       )

      VALUES

       (

        @firstEmail

        ,@FirstOrderNO

        ,@FirstTrackingNO

       ); 

      SET @@email = @NextEmail;

      SET @@orderNO = @NextOrderNO;

      SET @@trackingNO = @NextTrackingNO;

    END

    ELSE

    BEGIN

      SET @@email = @NextEmail;

      SET @@orderNO = @FirstOrderNO+'、'+@NextOrderNO;

      SET @@trackingNO = @FirstTrackingNO+'、'+@NextTrackingNO;

    END

     

     

     

    FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO,@NextTrackingNO

    WHILE @@fetch_status=0

    BEGIN

      IF @NextEmail=@@email

      BEGIN

        IF (@NextOrderNO!=@@orderNO)

          SET @@orderNO = @@orderNO+'、'+@NextOrderNO

         

        PRINT 'orderNO:'+@@orderNO 

         

        IF (@@trackingNO!=@NextTrackingNO)

          SET @@trackingNO = @@trackingNO+'、'+@NextTrackingNO

         

        PRINT 'trackingNO：'+@@trackingNO

      END

      ELSE

      BEGIN

        INSERT INTO Email20140725Test

         (

          email

          ,OrderNo

          ,TrackingNo

         )

        VALUES

         (

          @@email

          ,@@orderNO

          ,@@trackingNO

         );

        SET @@email = @NextEmail;

        SET @@orderNO = @NextOrderNO;

        SET @@trackingNO = @NextTrackingNO;

      END

      FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO;

    END

    CLOSE email; --關閉游標

    DEALLOCATE email; --釋放游標

  END

END