Sql Server cursor 的使用处理重复数据 动态拼接 SQL语句

/************************************************************ * Code formatted by setyg * Time: 2014/7/29 10:04:44 ************************************************************/CREATE PROC [dbo].[HandleEmailRepeat] (@TableNameSelect VARCHAR(100), @TableNameIn VARCHAR(100))AS DECLARE @sql NVARCHAR(2000);SET @sql =     'DECLARE EmailHandle CURSOR  FOR    SELECT REPLACE(email,'' '','''') email          ,e.OrderNo          ,e.TrackingNo    FROM   ' + QUOTENAME(@TableNameSelect) +    ' AS e        ORDER BY           e.email     'EXEC (@sql)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 EmailHandle;        FETCH NEXT FROM EmailHandle INTO @firstEmail,@FirstOrderNO, @FirstTrackingNO;        FETCH NEXT FROM EmailHandle INTO @NextEmail,@NextOrderNO, @NextTrackingNO;        IF @NextEmail != @firstEmail        BEGIN            DECLARE @ssql NVARCHAR(2000);            SET @ssql = '            INSERT INTO ' + QUOTENAME(@TableNameIn) +                '              (email,OrderNo,TrackingNo,[status])            VALUES              (''' + @firstEmail + ''',''' + @FirstOrderNO + ''',''' + @FirstTrackingNO                 +                ''',0 )';             EXEC (@ssql);            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 EmailHandle INTO @NextEmail,@NextOrderNO,@NextTrackingNO        WHILE @@fetch_status = 0        BEGIN            IF @NextEmail = @@email            BEGIN                PRINT 'email:' + @@email ;                IF PATINDEX('%' + @NextOrderNO + '%', @@orderNO) = 0                    SET @@orderNO = @@orderNO + '、' + @NextOrderNO                                PRINT 'orderNO:' + @@orderNO                                  IF PATINDEX('%' + @NextTrackingNO + '%', @@trackingNO) = 0                    SET @@trackingNO = @@trackingNO + '、' + @NextTrackingNO                                PRINT 'trackingNO：' + @@trackingNO            END            ELSE            BEGIN                DECLARE @sssql NVARCHAR(2000);                SET @sssql = '                            INSERT INTO ' + QUOTENAME(@TableNameIn) +                    '                              (email,OrderNo,TrackingNo,[status])                            VALUES                              (''' + @@email + ''',''' + @@orderNO + ''',''' + @@trackingNO                    + ''',0 )';                EXEC (@sssql);                                                SET @@email = @NextEmail;                SET @@orderNO = @NextOrderNO;                SET @@trackingNO = @NextTrackingNO;            END            FETCH NEXT FROM EmailHandle INTO @NextEmail,@NextOrderNO, @NextTrackingNO;        END        CLOSE EmailHandle; --关闭游标        DEALLOCATE EmailHandle; --释放游标                IF @@email = @NextEmail        BEGIN            DECLARE @ssssql NVARCHAR(2000);            SET @ssssql = 'INSERT INTO ' + QUOTENAME(@TableNameIn) +                '(email,OrderNo,TrackingNo,[status])VALUES                (''' + @@email + ''',''' + @@orderNO + ''',''' + @@trackingNO+ ''',0 )';            EXEC (@ssssql);        END    ENDEND       GO