sql server例子处理

以下为当时所做的项目一些垃圾数据处理。当时不知道如何调试sql server 2005(2008)，就单独写了一段代码处理，也未使用存储过程。

DROP TABLE #ContainerTemp
DECLARE @varRowCountID int
DECLARE @varContainerNo nvarchar(30)
DECLARE @varRow int
DECLARE @varRowLowYear int
DECLARE @varRowTotal int
DECLARE @LastCycle int
DECLARE @ref nvarchar(30)
create table #ContainerTemp(
RowID int identity(1,1) not null,
ContainerNo nvarchar(30))
INSERT INTO #ContainerTemp(ContainerNo) SELECT  DISTINCT MContainerNo FROM td_LGMV_Movement ORDER BY MContainerNo
SELECT @varRow = max(RowID) FROM #ContainerTemp
--SET @varRow = 52
SET @varRowCountID = 35001
WHILE(@varRowCountID<=53409)
BEGIN
 SELECT @varContainerNo = ContainerNo FROM #ContainerTemp WHERE RowID = @varRowCountID
 --PRINT 'ContainerNo'
 --PRINT @varContainerNo
 
 SELECT @LastCycle = max(MCycle) FROM (
 SELECT DISTINCT MContainerNo,MCycle FROM td_LGMV_Movement INNER JOIN #ContainerTemp ON
 td_LGMV_Movement.MContainerNo = #ContainerTemp.ContainerNo WHERE RowID = @varRowCountID AND MEventDT<'2008-01-01 00:00:000') a --ORDER BY MEventDT,MCycle
 --PRINT 'RowCountID'
 --PRINT @varRowCountID
 --PRINT 'LastCycle'
 --PRINT @LastCycle
 
 SELECT @varRowLowYear = count(*) FROM (
 SELECT DISTINCT MContainerNo,MCycle FROM td_LGMV_Movement INNER JOIN #ContainerTemp ON
 td_LGMV_Movement.MContainerNo = #ContainerTemp.ContainerNo WHERE RowID = @varRowCountID AND MEventDT<'2008-01-01 00:00:000') a --ORDER BY MEventDT,MCycle
 --PRINT 'RowLowYear'
 --PRINT @varRowLowYear
 
 SELECT @varRowTotal = count(*) FROM (
 SELECT DISTINCT MContainerNo,MCycle FROM td_LGMV_Movement INNER JOIN #ContainerTemp ON
 td_LGMV_Movement.MContainerNo = #ContainerTemp.ContainerNo WHERE RowID = @varRowCountID) a --ORDER BY MEventDT,MCycle
 --PRINT 'RowTotal'
 --PRINT @varRowTotal
 
 --08年之后无动态
 IF(@varRowLowYear = @varRowTotal)
 BEGIN
  IF(@LastCycle IS NULL)
   SET @LastCycle = 0
  ELSE
  BEGIN
   IF(@LastCycle = 0)
   BEGIN
    --PRINT '处理08年之后无动态，只有一个Cycle的例子' + cast(@LastCycle as nvarchar(6))
    UPDATE td_LGMV_Movement SET MCycle = 1 WHERE MContainerNo = @varContainerNo
   END
   ELSE
   BEGIN
    --PRINT '处理08年之后无动态，有多个Cycle的例子' + cast(@LastCycle as nvarchar(6))
    DELETE FROM td_LGMV_Movement WHERE td_LGMV_Movement.MContainerNo = @varContainerNo AND MCycle < @LastCycle
    --PRINT @varContainerNo
    UPDATE td_LGMV_Movement SET MCycle = MCycle - @LastCycle + 1 WHERE MContainerNo = @varContainerNo
   END
  END
 END
 ELSE--08年之后有动态
 BEGIN
  IF(@LastCycle IS NOT NULL)
  BEGIN
   --PRINT '处理08年之后有动态，有多个Cycle的例子' + cast(@LastCycle as nvarchar(6))
   DELETE FROM td_LGMV_Movement WHERE td_LGMV_Movement.MContainerNo = @varContainerNo AND MCycle < @LastCycle + 1
   UPDATE td_LGMV_Movement SET MCycle = MCycle - @LastCycle  WHERE MContainerNo = @varContainerNo
  END
 END
 --PRINT @LastCycle
 --PRINT @varRowLowYear
 --PRINT @varRowTotal
 
 SELECT TOP 1 @ref = MMovementRef FROM td_LGMV_Movement
  WHERE MContainerNo = @varContainerNo ORDER BY MEventDT ASC
 UPDATE td_LGMV_Movement SET MPreMovememtRef = NULL WHERE MMovementRef = @ref
 --PRINT @ref
 SET @varRowCountID = @varRowCountID + 1
END