sql server存储过程实例

   刚开始能够自己独立写存储过程了，遇到一些不懂的问题就问同事或者上网查，现在已经可以写巨长的存储过程了，现在把写过的实例记录在这。

1
/****** Object:  Stored Procedure dbo.AllTimeData_1    Script Date: 2004-11-12 13:18:28 ******/
CREATE PROCEDURE AllTimeData
@orderno  int
AS
SELECT d.orderno,cast(d.sumli*12 as numeric(18,2))as '平均流量',
'平均速度'=cast(case d.sumli when 0 then 0 else
case  when d.sumsp/d.sumli>100 then cast(rand()*(80-30)+30 as decimal)
else d.sumsp/d.sumli end
end as numeric(18,2)),
'平均轴数'=cast(case d.sumli when 0 then 0 else d.sumzs/d.sumli end as numeric(18,2)),
'平均轴重'=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(SmallTruckTraffic,0))+sum(isnull(MiddleTruckSpeed,0)*isnull(MiddleTruckTraffic,0))+sum(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(SmallCarTraffic,0))+sum(isnull(BigCarAxes,0)*isnull(BigCarTraffic,0))+sum(isnull(TrailerAxes,0)*isnull(TrailerTraffic,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(BigTractorTraffic,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.timeno = C.timeno And b.orderno =@orderno
group by b.orderno
             
) d
GO

2
/****** Object:  Stored Procedure dbo.AllTimeDetailData    Script Date: 2004-11-12 13:18:28 ******/
CREATE PROCEDURE AllTimeDetailData
@orderno int
AS

select ' 小 货 '+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,
' 中 货 '+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,
' 大 货 '+CAST(cast(c1 as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(c2  as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(c3 as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(c4 as numeric(18,2)) AS varchar(20)) as BigTruck,
' 小 客 '+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,
' 大 客 '+CAST(cast(e1 as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(e2  as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(e3 as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(e4 as numeric(18,2)) AS varchar(20)) as BigCar,
' 拖 挂 '+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,
' 小 拖 '+CAST(cast(g1 as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(g2  as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(g3 as numeric(18,2)) AS varchar(20))+'  '+' '+' '+ CAST(cast(g4 as numeric(18,2)) AS varchar(20)) as SmallTractor,
' 大 拖 '+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='超声波' 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='超声波' 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(MiddleTruckTraffic),2) end,0),
b4=isnull(case sum(MiddleTruckTraffic) when 0 then 0 else round(sum(MiddleTruckWeight*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='超声波' 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(SmallCarTraffic) when 0 then 0 else
case  when equitype='超声波' then round((sum(SmallCarSpeed*SmallCarTraffic)/10)/sum(SmallCarTraffic),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(SmallCarTraffic) when 0 then 0 else round(sum(SmallCarWeight*SmallCarTraffic)/sum(SmallCarTraffic),2) end,0),

 sum(isnull(BigCarTraffic,0)) as e1,
e2=isnull(case sum(BigCarTraffic) when 0 then 0 else
case  when equitype='超声波' then round((sum(BigCarSpeed*BigCarTraffic)/10)/sum(BigCarTraffic),2)
else round(sum(BigCarSpeed*BigCarTraffic)/sum(BigCarTraffic),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='超声波' 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(TrailerTraffic),2) end,0),
f4=isnull(case sum(TrailerTraffic) when 0 then 0 else round(sum(TrailerWeight*TrailerTraffic)/sum(TrailerTraffic),2) end,0),
 sum(isnull(SmallTractorTraffic,0))*12 as g1,
g2=isnull(case sum(SmallTractorTraffic) when 0 then 0 else
case  when equitype='超声波' 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(SmallTractorTraffic),2) end,0),
g4=isnull(case sum(SmallTractorTraffic) 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='超声波' 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*BigTractorTraffic)/sum(BigTractorTraffic),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 <(DATEPART (hour,GETDATE ( ))+1)*60) And  A.statdate = '20'+convert(varchar(10),GETDATE ( ),12)
 and b.orderno= @orderno
group by equitype) Temptab
GO


3

/****** Object:  Stored Procedure dbo.AxesData_Bar    Script Date: 2004-11-12 13:18:28 ******/
CREATE PROCEDURE AxesData_Bar AS
SELECT  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*SmallCarTraffic)/sum(SmallCarTraffic),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(BigTractorTraffic) when 0 then 0 else round(sum(BigTractorAxes*BigTractorTraffic)/sum(BigTractorTraffic),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(sum(ContainerAxes*ContainerTraffic)/sum(ContainerTraffic),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 <(DATEPART (hour,GETDATE ( ))+1)*60) And  A.statdate = '20'+convert(varchar(10),GETDATE ( ),12)
group by  b.orderno
GO


4
/****** Object:  Stored Procedure dbo.proCreatTable    Script Date: 2004-11-12 13:18:29 ******/
CREATE PROCEDURE proCreatTable
 @CreateTbName varchar(50),
 @CreatePrimary varchar(30)
AS
declare @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 ,[MiddleTruckAxes] [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 ,[SmallCarTraffic] [int] NULL ,[SmallCarSpeed] [decimal](18, 2) NULL , 
[SmallCarAxes] [decimal](18, 1) NULL , [SmallCarWeight] [decimal](18, 2) NULL ,[Prep29] [int] NULL ,[Prep30] [int] NULL ,[BigCarTraffic] [int] NULL ,[BigCarSpeed] [decimal](18, 2) NULL ,[BigCarAxes] [decimal](18, 1) NULL , [BigCarWeight] [decimal](18, 2) NULL ,[Prep35] [int] NULL ,[Prep36] [int] NULL ,[TrailerTraffic] [int] NULL ,[TrailerSpeed] [decimal](18, 2) NULL , [TrailerAxes] [decimal](18, 1) NULL ,[TrailerWeight] [decimal](18, 2) NULL ,[Prep41] [int] NULL ,[Prep42] [int] NULL ,
[SmallTractorTraffic] [int] NULL ,[SmallTractorSpeed] [decimal](18, 2) NULL , [SmallTractorAxes] [decimal](18, 1) NULL ,[SmallTractorWeight] [decimal](18, 2) NULL , [Prep47] [int] NULL ,[Prep48] [int] 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 ,[ContainerTraffic] [int] NULL ,[ContainerSpeed] [decimal](18, 2) NULL ,[ContainerAxes] [decimal](18, 1) NULL , [ContainerWeight] [decimal](18, 2) NULL ,[Prep66] [int] NULL ,[Prep67] [int] NULL ,[Prep68] [int] NULL ,[Prep69] [int] NULL ,[Prep70] [int] NULL ,[Prep71] [int] NULL ,[Prep72] [int] NULL ,
[Prep73] [int] NULL ,[Prep74] [int] NULL ,[Prep75] [int] NULL ,[Prep76] [int] NULL ,[Prep77] [int] NULL ,[Prep78] [int] NULL ,[Prep79] [int] NULL ,[Prep80] [int] NULL ,[Prep81] [int] NULL ,[Prep82] [int] NULL ,[Prep83] [int] NULL ,[Prep84] [int] NULL ,[Prep85] [int] NULL ) ON [PRIMARY]'
exec(@sql)

set @sql='ALTER TABLE '+@CreateTbName+' WITH NOCHECK ADD CONSTRAINT '+@CreatePrimary+' PRIMARY KEY  CLUSTERED
 ([StationNO],[DataPackageType],[StatDate],[StatDistantTime],[TimeNO],[WayNO])  ON [PRIMARY] '
exec(@sql)
GO


5
/****** Object:  Stored Procedure dbo.proSaveGPSHistoryData    Script Date: 2004-11-12 13:18:29 ******/
CREATE PROCEDURE proSaveGPSHistoryData
AS
--检查表
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>=@b
OPEN GPSmonth_cursor

FETCH NEXT FROM GPSmonth_cursor  INTO @statmonth
select @statmonth

WHILE @@FETCH_STATUS = 0
BEGIN
set @TbName='TgpsDataOrg_'+@statmonth
set @PrimaryKey='PK_TgpsDataOrg_'+@statmonth
if not exists (select * from dbo.sysobjects where id = object_id(@TbName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
--不存在,建表       
 exec proCreatGPSTable @TbName,@Primarykey 
 --select @TbName
 IF (@@error <> 0)
 BEGIN
   RAISERROR ('no creattable-%s',16, 1,@TbName)
   ROLLBACK TRANSACTION
 END
end

--根据字段prep85为1的插入数据
set @sql='insert into '+@TbName+' select * from TgpsDataOrg where  substring(CONVERT(varchar(10),ArrivedTime,120),1,4)+substring(CONVERT(varchar(10),ArrivedTime,120),6,2)='''+@statmonth+''''
 +' and ArrivedTime<'''+@d+''' and ArrivedTime>='''+@b+''''
--select @sql
exec(@sql)
IF (@@error <> 0)
BEGIN
  RAISERROR ('no do sql==%s',16, 1,@sql)
  ROLLBACK TRANSACTION
END

FETCH NEXT FROM GPSmonth_cursor INTO @statmonth
select @statmonth
End
CLOSE GPSmonth_cursor
DEALLOCATE GPSmonth_cursor

--根据字段prep85为1的删除数据
set @sql='delete from TgpsDataOrg where  ArrivedTime<'''+@d+''' and ArrivedTime>='''+@b+''''
--select @sql
exec(@sql)
IF (@@error <> 0)
BEGIN
  RAISERROR ('no do sql==%s',16, 1,@sql)
  ROLLBACK TRANSACTION
END

COMMIT TRANSACTION
GO


6
/****** Object:  Stored Procedure dbo.proSaveHistoryData    Script Date: 2004-11-12 13:18:29 ******/
CREATE PROCEDURE proSaveHistoryData
AS
--检查表
declare @sql nvarchar(4000)
declare @b varchar(6)
set @b=substring(CONVERT(varchar(7),dateadd(month,-2,getdate()),111),1,4)+substring(CONVERT(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(CONVERT(varchar(10),dateadd(day,-7,getdate()),111),6,2)+substring(CONVERT(varchar(10),dateadd(day,-7,getdate()),111),9,2)
--select @d

BEGIN TRANSACTION
--符合条件的,修改字段prep85为1
set @sql='update TB_TrafficInfo set prep85=1 from TB_TrafficInfo where statdate<='''+@d+''''+' and statdate>='''+@b+'01'+''''
--select @sql
exec(@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 @statmonth

WHILE @@FETCH_STATUS = 0
BEGIN
set @TbName='Tb_TrafficInfo_'+@statmonth
set @PrimaryKey='PK_TB_TrafficInfo_'+@statmonth
if not exists (select * from dbo.sysobjects where id = object_id(@TbName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
--不存在,建表       
 exec proCreatTable @TbName,@Primarykey 
 IF (@@error <> 0)
 BEGIN
   RAISERROR ('no creattable-%s',16, 1,@TbName)
   ROLLBACK TRANSACTION
 END
end

--根据字段prep85为1的插入数据
set @sql='insert into '+@TbName+' select * from TB_TrafficInfo where prep85=1 and substring(statdate,1,6)='''+@statmonth+''''
--select @sql
exec(@sql)
IF (@@error <> 0)
BEGIN
  RAISERROR ('no do sql==%s',16, 1,@sql)
  ROLLBACK TRANSACTION
END

FETCH NEXT FROM statmonth_cursor
   INTO @statmonth
End
CLOSE statmonth_cursor
DEALLOCATE statmonth_cursor

--根据字段prep85为1的删除数据
set @sql='delete from TB_TrafficInfo where prep85=1 '
--select @sql
exec(@sql)
IF (@@error <> 0)
BEGIN
  RAISERROR ('no do sql==%s',16, 1,@sql)
  ROLLBACK TRANSACTION
END

COMMIT TRANSACTION
GO