/************************************************************ * 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, @firstEmail VARCHAR, @FirstOrderNO VARCHAR, @FirstTrackingNO VARCHAR (), @NextEmail VAR
CHAR (+), @ @orderNO VARCHAR, @NextOrderNO VARCHAR, @ @trackingNO VARCHAR (300)
, @NextTrackingNO VARCHAR BEGIN OPEN email;
FETCH NEXT from email into @firstEmail, @FirstOrderNO, @FirstTrackingNO;
FETCH NEXT from email into @NextEmail, @NextOrderNO, @NextTrackingNO;
IF @NextEmail!= @firstEmail BEGININSERT 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 to @NextEmail, @NextOrderNO, @NextTrackingNO while @ @fetch_status =0 Begin if @NextEmail =@ @email begin if (@NextOrderNO!=@ @orderNO) SET @ @orderNO = @ @or derno+ ', ' + @NextOrderNO PRINT ' OrderNO: ' +@ @orderNO IF (@ @trackingNO!= @NextTrackingNO
SET @ @trackingNO = @ @trackingNO + ', ' + @NextTrackingNO PRINT ' Trackingno: ' +@ @trackingNO End ELSE BEGIN inserts into email20140725test (email, OrderNo , Trackingno) VALUES (@ @email, @ @orderNO, @ @trackin
GNO);
SET @ @email = @NextEmail;
SET @ @orderNO = @NextOrderNO;
SET @ @trackingNO = @NextTrackingNO;
End FETCH NEXT from email into @NextEmail, @NextOrderNO, @NextTrackingNO; End Close email; --close cursor deallocate email; --Release cursor end