Just started to write a memory process independently, I met some questions that I didn't understand, I asked my colleague or online, and I can now write a huge stored procedure, and now write the instance written here. 1 / ****** Object: Stored Procedure DBO.ALLTIMEDATA_1 Script Date: 2004-11-12 13:18:28 ****** / Create Procedure AllTimedata @ Orderno INTASSELECT D.Orderno, Cast (D. Sumli * 12 As Numeric (18, 2)) AS 'average traffic', 'average speed' = Cast (Case D.Sumli when 0 Else Case When D. Sumsp / D.SUMLI> 100 TEN CAST (Rand () * (80-30) 30 as decimal) Else D.SUMSP / D.SUMLI Endend As Numeric (18, 2)), 'average number of axes' = CAST (Case D. Sumli When 0 Then 0 Else D.Sumzs / D .SUMLI END As Numeric (18, 2)), 'average axis weight' = CAST (Case D.SUMLI WHEN 0 THEN 0 Else D.Sumzz / D.SUMLI END As Numeric (18, 2)) from (SELECT B. orderno, (sum (isnull (SmallTruckTraffic, 0)) sum (isnull (MiddleTruckTraffic, 0)) sum (isnull (BigTruckTraffic, 0)) sum (isnull (SmallCarTraffic, 0)) sum (isnull (TrailerTraffic, 0 )) sum (isnull (SmallTractorTraffic, 0)) sum (isnull (BigTractorTraffic, 0)) sum (isnull (MotorcycleTraffic, 0)) sum (isnull (ContainerTraffic, 0)) sum (isnull (BigCarTraffic, 0 ))) AS SUMLI (Sum (isnull (SMALLTRUCKSPEED, 0) * Isnull (SmallTruckTraft, 0)) SUM (Isnull (MiddltruckSpeed, 0) * Isnull (MiddleTruckTraffic, 0)) S um (isnull (BigTruckSpeed, 0) * isnull (BigTruckTraffic, 0)) sum (isnull (SmallCarSpeed, 0) * isnull (SmallCarTraffic, 0)) sum (isnull (BigCarSpeed, 0) * isnull (BigCarTraffic, 0)) sum (isnull (TrailerSpeed, 0) * isnull (TrailerTraffic, 0)) sum (isnull (SmallTractorSpeed, 0) * isnull (SmallTractorTraffic, 0)) sum (isnull (BigTractorSpeed, 0) * isnull (BigTractorTraffic, 0) )) AS SUMSP,
(Sum (isnull (SmallTruckAxes, 0) * isnull (SmallTruckTraffic, 0)) sum (isnull (MiddleTruckAxes, 0) * isnull (MiddleTruckTraffic, 0)) sum (isnull (BigTruckAxes, 0) * isnull (BigTruckTraffic, 0) ) sum (isnull (smallcaraxes, 0) * isnull (smallcartraft, 0)) SUM (isnull (Bigcaraxes, 0) * Isnull (Bigcartraft, 0)) Sum (isnull (TraileRaxes, 0) * Isnull (Trailertraft, 0 )) sum (isnull (SmallTractorAxes, 0) * isnull (SmallTractorTraffic, 0)) sum (isnull (BigTractorAxes, 0) * isnull (BigTractorTraffic, 0))) as sumzs, (sum (isnull (SmallTruckWeight, 0) * isnull (SmallTruckTraffic, 0)) sum (isnull (MiddleTruckWeight, 0) * isnull (MiddleTruckTraffic, 0)) sum (isnull (BigTruckWeight, 0) * isnull (BigTruckTraffic, 0)) sum (isnull (SmallCarWeight, 0) * isnull (SmallCarTraffic, 0)) sum (isnull (BigCarWeight, 0) * isnull (BigCarTraffic, 0)) sum (isnull (TrailerWeight, 0) * isnull (TrailerTraffic, 0)) sum (isnull (SmallTractorWeight, 0 ) * Isnull (SmallTractortRaffic, 0) SUM (Isnull (BigTractorWeight, 0) * Isnull (BigTractRaffic, 0))) AS SUMZZ
FROM TB_TrafficInfo a, TB_Stationinfo b, (select a.stationno, a.statdate, max (b.timeno) as timeno from (select stationno, max (statdate) as statdate from TB_TrafficInfo group by stationno) as a, tb_trafficinfo as b where a .stationno = b.stationno and a.statdate = b.statdate group by a.stationno, a.statdate) C
WHERE A.STATIONNO = B.Stationno and a.stationno = C.Stationno and a.statdate = C.Statdate and a.timeo = C.TimeNo and B.Orderno = @ Orderno Group by B.Orderno) DGO2 / *** *** Object: Stored Procedure DBO.ALLTIMEDETAILDATA Script Date: 2004-11-12 13:18:28 ****** / CREATE Procedure AllTimedetAildata @ Orderno Intas
SELECT 'small goods' cast (CAST (A1 AS Numeric (18, 2)) AS VARCHAR (20)) '' '' '' Cast (Cast (A2 AS Numeric (18, 2)) as varchar (20)) '' '' CAST (CAST (A4 AS Numeric (18, 2)) AS VARCHAR (20)) AS SMALLTRUCK, 'China Cast (Cast (B1 AS Numeric (18 , 2)) AS VARCHAR (20)) '' '' '' CAST (CAST (B2 AS Numeric (18, 2)) AS VARCHAR (20)) '' ' ' CAST (CAST (B3 As Numeric (18, 2)) AS VARCHAR (20)) '' '' '' Cast (CAST (B4 AS Numeric (18, 2)) As Varchar (20)) AS MiddleTruck, 'Big goods' Cast (CAST (CAST (CAST (20)) ' ' ' ' ' Cast (Cast (Cast As Numeric (18, 2)) as varchar ( 20)) '' '' ' Cast (CAST (CAST (CAST (CAST (CAST (C3 AS Numeric (18, 2)) AS VARCHAR (20)) ' ' ' Cast (CAST (CAST (CAST (CAST (CAST (CAST (CAST (C4 AS NuMERIC) 18, 2)) AS VARCHAR (20)) AS Bigtruck, 'Trustee' Cast (CAST (D1 AS Numeric (18, 2)) AS VARCHAR (20)) '' '' ' Cast ( CAST (D2 As Numeric (18, 2)) AS VARCHAR (20)) '' '' '' CAST (CAST (D3 AS Numeric (18, 2)) as varchar (20)) '' '' '' Cast (CAST (D4 As Numeric (18, 2)) AS VARCHAR (20)) AS SMALLCAR, 'Gas' Cast (Cast (E1 AS Numeric (18, 2)) as Var CHAR (20)) '' '' ' CAST (CAST (E2 As Numeric (18, 2)) AS VARCHAR (20)) ' ' ' '' Cast (Cast (E3 AS Numeric (18, 2)) as varchar (20)) '' ''
'' Cast (E4 AS Numeric (18, 2)) As Varchar (20)) AS Bigcar, 'Drag' Cast (Cast (F1 AS Numeric (18, 2)) as varchar (20)) '' ' ' ' CAST (CAST (F2 As Numeric (18, 2)) AS VARCHAR (20)) ' ' ' ' ' ' Cast (Cast (F3 AS Numeric (18, 2) ) AS VARCHAR (20)) '' '' '' CAST (CAST (F4 AS Numeric (18, 2)) AS VARCHAR (20)) AS Trailer, 'Small Drag' Cast (Cast (G1 AS Numeric (18, 2)) AS VARCHAR (20)) '' '' '' CAST (CAST (G2 As Numeric (18, 2)) AS VARCHAR (20)) '' ' ' ' Cast (G3 As Numeric (18, 2)) AS VARCHAR (20)) ' ' ' ' ' ' Cast (CAST (G4 AS Numeric (18, 2)) as varchar (20)) AS SmallTractor, 'Drag' Cast (Cast (H1 As Numeric (18, 2)) AS VARCHAR (20)) '' '' Cast (Cast (H2 AS Numeric (18, 2)) As VARCHAR (20)) '' '' '' CAST (Cast (H3 AS Numeric (18, 2)) AS VARCHAR (20)) '' '' '' Cast (Cast (H4 As Numeric (18, 2)) As Varchar (20)) as BigTractortRaffic
from (SELECT sum (isnull (SmallTruckTraffic, 0)) as a1, a2 = isnull (case sum (SmallTruckTraffic) when 0 then 0 else case when equitype = 'ultrasonic' then round ((sum (SmallTruckSpeed * SmallTruckTraffic) / 10) / sum (SmallTruckTraffic), 2) else round (sum (SmallTruckSpeed * SmallTruckTraffic) / sum (SmallTruckTraffic), 2) end end, 0), a3 = isnull (case sum (SmallTruckTraffic) when 0 then 0 else round (sum (SmallTruckAxes * SmallTruckTraffic) / sum (SmallTruckTraffic), 2) end, 0), a4 = isnull (case sum (SmallTruckTraffic) when 0 then 0 else round (sum (SmallTruckWeight * SmallTruckTraffic) / sum (SmallTruckTraffic), 2) end, 0), sum (isnull (MiddleTruckTraffic, 0)) as b1, b2 = isnull (case sum (MiddleTruckTraffic) when 0 then 0 else case when equitype = 'ultrasonic' then round ((sum (MiddleTruckSpeed * MiddleTruckTraffic) / 10) / sum (MiddleTruckTraffic ), 2) else round (sum (MiddleTruckSpeed * MiddleTruckTraffic) / sum (MiddleTruckTraffic), 2) end end, 0), b3 = isnull (case sum (MiddleTruckTraffic) when 0 then 0 else round (sum (MiddleTruckAxes * MiddleTruckTraffic) / SUM (MiddleTruckTraff), 2) End, 0), B4 = ISNULL (Case SUM (Middle Sum (Middle SUM (MiddRUCKWEIGHT * MIDDLETRUCKTRAFFIC) / SUM (MiddletruckTraffic), 2) End, 0),
sum (isnull (BigTruckTraffic, 0)) as c1, c2 = isnull (case sum (BigTruckTraffic) when 0 then 0 else case when equitype = 'ultrasonic' then round ((sum (BigTruckSpeed * BigTruckTraffic) / 10) / sum (BigTruckTraffic ), 2) else round (sum (BigTruckSpeed * BigTruckTraffic) / sum (BigTruckTraffic), 2) end end, 0), c3 = isnull (case sum (BigTruckTraffic) when 0 then 0 else round (sum (BigTruckAxes * BigTruckTraffic) / sum (BigTruckTraffic), 2) end, 0), c4 = isnull (case sum (BigTruckTraffic) when 0 then 0 else round (sum (BigTruckWeight * BigTruckTraffic) / sum (BigTruckTraffic), 2) end, 0), sum (isnull (SmallCartraffic, 0)) AS D1, D2 = ISNULL (Case Sum (Smallcartraft) When 0 Then 0 else Case When Equitype = 'Ultrasound' Ten Round ((SMALLCARSPEED * SMALLCARTRAFFIC) / 10) / SUM (SmallCartRaff), 2 ) else round (sum (SmallCarSpeed * SmallCarTraffic) / sum (SmallCarTraffic), 2) end end, 0), d3 = isnull (case sum (SmallCarTraffic) when 0 then 0 else round (sum (SmallCarAxes * SmallCarTraffic) / sum (SmallCarTraffic ), 2) end, 0), D4 = isnull (case sum (smallcartraft) When 0 Then 0 else round (SMALLCA Rweight * smallcartraft / sum (smallcartraft), 2) end, 0), SUM (isnull (Bigcartraft, 0)) AS E1, E2 = ISNULL (Case Sum (Bigcartraft) when 0 Then 0 else case when quitype = 'Ultrasound' Then Round ((Bigcarspeed * Bigcartraft) / 10) / SUM (Bigcartraft), 2) Else Round (SUM (Bigcarspeed * Bigcartraft) / SUM (Bigcartraft), 2) End end, 0), E3 = isnull (Case SUM (BigCarTraffic) when 0 then 0 else round (sum (BigCarAxes * BigCarTraffic) / sum (BigCarTraffic), 2) end, 0), e4 = isnull (case sum (BigCarTraffic) when 0 then 0 else round (sum (BigCarWeight * BigCarTraffic ) / SUM (BIGCARTRAFFIC), 2) End, 0),
sum (isnull (TrailerTraffic, 0)) as f1, f2 = isnull (case sum (TrailerTraffic) when 0 then 0 else case when equitype = 'ultrasonic' then round ((sum (TrailerSpeed * TrailerTraffic) / 10) / sum (TrailerTraffic ), 2) else round (sum (TrailerSpeed * TrailerTraffic) / sum (TrailerTraffic), 2) end end, 0), f3 = isnull (case sum (TrailerTraffic) when 0 then 0 else round (sum (TrailerAxes * TrailerTraffic) / SUM (Trailertraft), 2) end, 0), F4 = ISNULL (Case Sum (Trailert * Trailertraft) / SUM (Trailertraft) / SUM (Trailertraft), 2) End, 0), SUM (Isnull (SmallTractorTraffic, 0)) * 12 as g1, g2 = isnull (case sum (SmallTractorTraffic) when 0 then 0 else case when equitype = 'ultrasonic' then round ((sum (SmallTractorSpeed * SmallTractorTraffic) / 10) / sum (SmallTractorTraffic) , 2) else round (sum (SmallTractorSpeed * SmallTractorTraffic) / sum (SmallTractorTraffic), 2) end end, 0), g3 = isnull (case sum (SmallTractorTraffic) when 0 then 0 else round (sum (SmallTractorAxes * SmallTractorTraffic) / sum (Smalltractraff), 2) end, 0), G4 = Isnull (Case Sum ) When 0 then 0 else round (sum (SmallTractorWeight * SmallTractorTraffic) / sum (SmallTractorTraffic), 2) end, 0), sum (isnull (BigTractorTraffic, 0)) as h1, h2 = isnull (case sum (BigTractorTraffic) when 0 then 0 else case when equitype = 'ultrasonic' then round ((sum (BigTractorSpeed * BigTractorTraffic) / 10) / sum (BigTractorTraffic), 2) else round (sum (BigTractorSpeed * BigTractorTraffic) / sum (BigTractorTraffic), 2) end end , 0), h3 = isnull (case sum (BigTractorTraffic) when 0 then 0 else round (sum (BigTractorAxes * BigTractorTraffic) / sum (BigTractorTraffic), 2) end, 0), h4 = isnull (case sum (BigTractorTraffic) when 0 Then 0 else round (Sum (BigTractorWeight * BigTractRaffic) / SUM (BigTractortRaffic), 2) end, 0)
From TB_TRAFFICINFO A, TB_STATIONINFO B Where A.StationNO = B.Stationno and (a.timeo> = datepart (hour, getdate ()) * 60 and a.timeno <(datepart (Hour, getdate ()) 1) * 60 ) And a.statdate = '20' conver (varchar (10), getdate (), 12) and b.orderno = @ORDERNOGROUP BY Equitype) Temptabgo3 / ****** Object: Stored Procedure DBo.Axesdata_bar script date : 2004-11-12 13:18:28 ****** / CREATE PROCEDURE AxesData_Bar ASSELECT b.orderno as orderno, SmallTruckAxes = isnull (case sum (SmallTruckTraffic) when 0 then 0 else round (sum (SmallTruckAxes * SmallTruckTraffic) / sum (SmallTruckTraffic), 2) end, 0), MiddleTruckAxes = isnull (case sum (MiddleTruckTraffic) when 0 then 0 else round (sum (MiddleTruckAxes * MiddleTruckTraffic) / sum (MiddleTruckTraffic), 2) end, 0), BigTruckAxes = isnull (case sum (BigTruckTraffic) when 0 then 0 else round (sum (BigTruckAxes * BigTruckTraffic) / sum (BigTruckTraffic), 2) end, 0), SmallCarAxes = isnull (case sum (SmallCarTraffic) when 0 then 0 else round (sum (Smallcaraxes * Smallcartraft / SUM (SmallCartraff), 2) End , 0), BigCarAxes = isnull (case sum (BigCarTraffic) when 0 then 0 else round (sum (BigCarAxes * BigCarTraffic) / sum (BigCarTraffic), 2) end, 0), TrailerAxes = isnull (case sum (TrailerTraffic) when 0 then 0 else round (sum (TrailerAxes * TrailerTraffic) / sum (TrailerTraffic), 2) end, 0), SmallTractorAxes = isnull (case sum (SmallTractorTraffic) when 0 then 0 else round (sum (SmallTractorAxes * SmallTractorTraffic) / sum (SmallTractorTraffic ), 2) end, 0), BigTractoraxes = ISNULL (Case SUM (BigTractraff) When 0 Then 0 Else Round (SUM (BigTractoS * BigTractraffic) / Sum (BigTractortRaff), 2) End, 0),
MotorcycleAxes = isnull (case sum (MotorcycleTraffic) when 0 then 0 else round (sum (MotorcycleAxes * MotorcycleTraffic) / sum (MotorcycleTraffic), 2) end, 0), ContainerAxes = isnull (case sum (ContainerTraffic) when 0 then 0 else round (containeRaxes * containertraft / sum (containertraft), 2) end, 0) from TB_TRAFFICINFO A, TB_STATIONINFO B Where a.stationno = B.Stationno and (a.timeno> = datepart (Hour, getdate ()) * 60 And A.TimeNo <(hour, getdate ()) 1) * 60) and a.statdate = '20' Convert (varchar (10), getdate (), 12) group by b.Ordernogo4 / ** **** Object: Stored Procedure dbo.proCreatTable Script Date: 2004-11-12 13:18:29 ****** / CREATE PROCEDURE proCreatTable @CreateTbName varchar (50), @CreatePrimary varchar (30) ASdeclare @sql Varchar (4000)
Set @ SQL = 'CREATE TABLE' @ CreateTBName '
([StationNO] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL, [DataPackageType] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL, [StatDate] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL, [StatDistantTime] [int] NOT NULL, [TIMENO] [INT] NOT NULL, [WAYNO] [INT] NOT NULL, [SMALLTRUCKTRAFFIC] [INT] NULL, [SmallTruckSpeed] [Decimal] (18, 2) Null, [SmallTruckaxes] [Decimal] (18, 1) NULL, [SMALLTRUCKWEIGHT] [DECIMAL] (18, 2) NULL, [PREP11] [INT] NULL, [PREP12] [INT] NULL, [MIDDLETRUCKTRAFFIC] [INT] NULL, [MiddletruckSpeed] [Decimal] (18, 2) NULL, [MiddleRuckaxes] [Decimal] (18, 1) NULL, [MiddleTruckWeight] [Decimal] (18, 2) NULL, [PREP17] [INT] NULL, [PREP18] [INT] NULL, [BIGTRUCKTRAFFIC] [ INT] NULL, [BIGTRUCKSPEED] [DECIMAL] (18, 2) NULL, [BIGTRUCKAXES] [Decimal] (18, 1) NULL, [BigtruckWeight] [Decimal] (18, 2) NULL, [PREP23] [INT] NULL [PreP24] [INT] NULL, [SmallCartraft] [INT] NULL, [SmallCarspeed] [Decimal] (18, 2) NULL, [SmallCaraxes] [Decimal] (18, 1) Null, [SmallCarweight] [Decimal] 18, 2) Nu LL, [PREP29] [INT] NULL, [PREP30] [INT] null, [Bigcartraft] [INT] NULL, [Bigcarspeed] [Decimal] (18, 2) NULL, [Bigcaraxes] [Decimal] (18, 1) NULL, [BigcarWeight] [Decimal] (18, 2) NULL, [PREP35] [INT] NULL, [PREP36] [INT] NULL, [TRALERTRAFFIC] [INT] null, [trailerspeed] [Decimal] (18, 2) NULL, [TRALERAXES] [Decimal] (18, 1) NULL, [TrailerWeight] [Decimal] (18, 2) NULL, [PREP41] [INT] null, [PREP42] [INT] NULL, [SmallTractRaffic] [INT] NULL, [SmallTractorspeed] [Decimal] (18, 2) NULL, [SmallTractoraxes] [Decimal] (18, 1) NULL, [SmallTractorWeight] [Decimal] (18, 2) NULL, [PREP47] [INT] NULL, [SMALL Prep48] NULL, [PREP49] [INT] NULL, [BigTractortRaffic] [INT] null, [BigTractorspeed] [Decimal] (18, 2) NULL, [BIGTRACTORAXES] [Decimal] (18,
1) NULL, [BIGTRACTORWEIGHT] [DECIMAL] (18, 2) NULL, [PREP54] [INT] NULL, [PREP55] [INT] NULL, [MOTORCYCLETRAFFIC] [INT] NULL, [MotorcycleSpeed] [Decimal] (18, 2) NULL, [Motorcycleaxes] [Decimal] (18, 1) NULL, [MotorcycleWeight] [Decimal] (18, 2) NULL, [PREP60] [INT] NULL, [PREP61] [INT] NULL, [Containertraft] [ INT] NULL, [Containerspeed] [Decimal] (18, 2) NULL, [ContaineRaxes] [Decimal] (18, 1) NULL, [ContainerWeight] [Decimal] (18, 2) NULL, [PREP66] [INT] NULL , [Pred] null, [pred69] [int] null, [preP70] [int] null, [pred71] [int] null, [pred72] [int] NULL, [PREP72] [INT] NULL, [PREP72] [INT] NULL, [ Prep73] NULL, [PREP74] [INT] null, [pred76] [int] null, [pred77] [int] null, [preP78] [int] null, [preP79] [int] null, [pred81] [int] null, [pred82] [int] null, [pred] [int] null, [preP84] [int] null, [preP85] [INT ] Null) on [primary] 'EXEC (@SQL) set @ SQL =' alter table ' @ createtbname ' with nocheck add constraint ' @ createprimary ' primary keyclustered ([S TATIONNO], [DataPackageType], [Statdate], [StatDistTime], [Timeno], [Wayno]) ON [PRIMARY] 'EXEC (@SQL) GO5 / ****** Object: Stored Procedure DBo.prosavegpshistoryData Script Date : 2004-11-12 13:18:29 ****** / CREATE Procedure ProsavegpshistoryDataAS - Checklist Declare @SQL NVARCHAR (4000) Declare @B VARCHAR (20) SET @ B = Convert (Varchar (10), Dateadd (Month, -2, getdate ()), 120) '00: 00: 00'select @B
Declare @d varchar (20) set @ d = convert (varchar (10), dateadd (day, -7, getdate ()), 120) '00: 00: 00'select @d
Begin Transaction
--declare @TbName varchar (50), @ PrimaryKey varchar (30) declare @statmonth varchar (7) DECLARE GPSmonth_cursor CURSOR FOR select Distinct (substring (CONVERT (varchar (10), ArrivedTime, 120), 1,4) substring (CONVERT (varchar (10), ArrivedTime, 120), 6,2)) from TgpsDataOrg where ArrivedTime <@d and ArrivedTime> = @ bOPEN GPSmonth_cursorFETCH NEXT fROM GPSmonth_cursor INTO @statmonthselect @statmonth
WHILE @@ FETCH_STATUS = 0BEGINset @ TbName = 'TgpsDataOrg _' @ statmonthset @ PrimaryKey = 'PK_TgpsDataOrg _' @ statmonthif not exists (select * from dbo.sysobjects where id = object_id (@TbName) and OBJECTPROPERTY (id, N'IsUserTable ' ) = 1) Begin - does not exist, build table exec protreatgpstable @ tbname, @ primarykey --select @tbname if (@@ Error <> 0) Begin Raiserror ('no createtable-% s', 16, 1, @ TBNAME ) ROLLBACK TRANSACTION Endend
- Insert data set @ SQL = 'INTO' @ tbname 'Select * from TGPSDataRG WHERE SUBSTRING (Convert (10), ArrivedTime, 120), 1, 4) Substring (Convert) (CONVERT) VARCHAR (10), ArrivedTime, 120), 6, 2) = '' ' @ statmonth ' '' 'and arrivedtime <' '' @ d '' 'and arrivedtime> =' '' @ b ' '' --select @sqlexec (@SQL) IF (@@ Error <> 0) Begin Raiserror ('NO DO SQL ==% S', 16, 1, @ SQL) ROLLBACK TRANSACTION END
Fetch next from gpsmonth_cursor @statmontHselect @statmonthendclose gpsmonth_cursordeallocate gpsmonth_cursor
- Delete data according to the field preP85 set @ SQL = 'delete from TGPSDataRG where arrivedtime <' ' @ d ' '' and arrivedtime> = '' ' @ b ' '' - select @sqlexec @SQL) IF (@@ Error <> 0) Begin Raiserror ('NO DO SQL ==% S', 16, 1, @ SQL) Rollback Transaction Endcommit TransactionGO6 / ****** Object: Stored Procedure DBo.prosavehistoryData Script Date: 2004-11-12 13:18:29 ****** / Create Procedure ProsavehistoryDataAs - Checklist Declare @SQL NVARCHAR (4000) Declare @B VARCHAR (6) Set @ b = Substring (VARCHAR) (7), Dateadd (Month, -2, Getdate (), 111), 1, 4) Substring (VARCHAR (7), Dateadd (Month, -2, Getdate (), 111), 6, 2) - SELECT @B
Declare @d varchar (8) set @ d = substring (convert (varchar (10), dateadd (day, -7, getdate ()), 111), 1, 4) Substring (VARCHAR (10), DateAdd (Day, -7, getdate ()), 111), 6, 2) Substring (varchar (10), dateadd (day, -7, getdate (), 111), 9, 2) - SELECT @D
Begin Transaction - Eligible, modified field preP85 is 1Set @ SQL = 'update tb_traftinfo set preP85 = 1 from tb_traftinfo where statdate <=' '' @ d '' ' ' and statid> = '' ' @ B '01 ' ' '' - select @sqlexec (@SQL) IF (@@ Error <> 0) Begin Raiserror ('NO DO SQL ==% S', 16, 1, @ SQL) ROLLBACK TRANSACTION END
--declare @TbName varchar (50), @ PrimaryKey varchar (30) declare @statmonth varchar (6) DECLARE statmonth_cursor CURSOR FOR select Distinct (substring (statdate, 1,6)) from TB_TrafficInfo where prep85 = 1 OPEN statmonth_cursor
FETCH NEXT FROM statmonth_cursor INTO @statmonthWHILE @@ FETCH_STATUS = 0BEGINset @ TbName = 'Tb_TrafficInfo _' @ statmonthset @ PrimaryKey = 'PK_TB_TrafficInfo _' @ statmonthif not exists (select * from dbo.sysobjects where id = object_id (@TbName) and OBJECTPROPERTY ( ID, N'ISUSERTABLE ') = 1) Begin - does not exist, build table EXEC ProcretTable @ TBNAME, @PrimaryKey if (@@ Error <> 0) Begin Raiserror (' no createtable-% s', 16, 1, @ TBNAME) ROLLBACK TRANSACTION Endend
- According to the field preP85 1, inserted data set @ SQL = 'INSERT INTO' @ TBNAME 'SELECT * from TB_TRAFFICINFO where preP85 = 1 and substring (statdate, 1, 6) =' ' @ statmonth ' '' --Select @sqlexec (@SQL) IF (@@ Error <> 0) Begin Raiserror ('NO DO SQL ==% S', 16, 1, @ SQL) ROLLBACK TRANSACTION END
Fetch next from statmony_cursor @statmonthendclose statmony stats Statmonth_cursor
- Delete data according to Field preP85 set @ SQL = 'delete from tb_traftinfo where prep85 = 1' --select @SQLEXEC (@sql) IF (@@ Error <> 0) Begin Raiserror ('no do sql == % s', 16, 1, @ SQL) Rollback Transaction end
Commit TransactionGO
