DECLARE @dateBegin datetime
DECLARE @dateEnd datetime
DECLARE @date datetime
SET @dateBegin = '2006-09-01'
SET @dateEnd = '2006-09-30'
SET @date = @dateBegin
while(@date<=@dateEnd)
begin
if (datepart(dw,@date)!=6 AND datepart(dw,@date)!=7 )
begin---------------一下为原sp--------------------
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp') DROP TABLE cchData_temp
--GO
SELECT * INTO cchData_temp FROM tag_Sep2006
WHERE TransDateTime >= @date AND TransDateTime < @date+1
--GO
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_chx') DROP TABLE cchData_temp_chx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_che') DROP TABLE cchData_temp_che
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_ehx') DROP TABLE cchData_temp_ehx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_ehe') DROP TABLE cchData_temp_ehe
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_lrx') DROP TABLE cchData_temp_lrx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_lre') DROP TABLE cchData_temp_lre
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tcx') DROP TABLE cchData_temp_tcx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tce') DROP TABLE cchData_temp_tce
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tlx') DROP TABLE cchData_temp_tlx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tle') DROP TABLE cchData_temp_tle
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_whx') DROP TABLE cchData_temp_whx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_whe') DROP TABLE cchData_temp_whe
--GO
select * into cchData_temp_chx from cchData_temp where Location like 'chx%'
select * into cchData_temp_che from cchData_temp where Location like 'che%'
--GO
select * into cchData_temp_ehx from cchData_temp where Location like 'ehx%'
select * into cchData_temp_ehe from cchData_temp where Location like 'ehe%'
--GO
select * into cchData_temp_lrx from cchData_temp where Location like 'lrx%'
select * into cchData_temp_lre from cchData_temp where Location like 'lre%'
--GO
select * into cchData_temp_tcx from cchData_temp where Location like 'tcx%'
select * into cchData_temp_tce from cchData_temp where Location like 'tce%'
--GO
select * into cchData_temp_tlx from cchData_temp where Location like 'tlx%'
select * into cchData_temp_tle from cchData_temp where Location like 'tle%'
--GO
select * into cchData_temp_whx from cchData_temp where Location like 'whx%'
select * into cchData_temp_whe from cchData_temp where Location like 'whe%'
--GO-- detail
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'v_cchData_20060901') DROP VIEW v_cchData_20060901
--GOelse
begin
declare @sql varchar(8000)
select @sql='
create view v_cchData_20060901 /****就是这里出的问题****/ --建立视图用动态语句完成
as
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_chx f, cchData_temp_lrx t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_lre f, cchData_temp_che t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_tce f, cchData_temp_ehe t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_whx f, cchData_temp_tlx t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_tle f, cchData_temp_whe t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime
'
exec (@sql)
end--GO
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_20060901_detail') DROP TABLE cchData_20060901_detail--GO
SELECT TagID, CONVERT(char(19),from_time,120) 'from_time', CONVERT(char(19),to_time,120) 'to_time', from_Location,to_Location,substring(from_Location,1,2) 'origination',substring(to_Location,1,2) 'destination', travel_time, period,getdate() 'adddate' INTO cchData_20060901_detail FROM v_cchData_20060901
---------sp结束--------------
end
set @date=@date+1
end
DECLARE @dateEnd datetime
DECLARE @date datetime
SET @dateBegin = '2006-09-01'
SET @dateEnd = '2006-09-30'
SET @date = @dateBegin
while(@date<=@dateEnd)
begin
if (datepart(dw,@date)!=6 AND datepart(dw,@date)!=7 )
begin---------------一下为原sp--------------------
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp') DROP TABLE cchData_temp
--GO
SELECT * INTO cchData_temp FROM tag_Sep2006
WHERE TransDateTime >= @date AND TransDateTime < @date+1
--GO
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_chx') DROP TABLE cchData_temp_chx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_che') DROP TABLE cchData_temp_che
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_ehx') DROP TABLE cchData_temp_ehx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_ehe') DROP TABLE cchData_temp_ehe
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_lrx') DROP TABLE cchData_temp_lrx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_lre') DROP TABLE cchData_temp_lre
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tcx') DROP TABLE cchData_temp_tcx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tce') DROP TABLE cchData_temp_tce
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tlx') DROP TABLE cchData_temp_tlx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tle') DROP TABLE cchData_temp_tle
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_whx') DROP TABLE cchData_temp_whx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_whe') DROP TABLE cchData_temp_whe
--GO
select * into cchData_temp_chx from cchData_temp where Location like 'chx%'
select * into cchData_temp_che from cchData_temp where Location like 'che%'
--GO
select * into cchData_temp_ehx from cchData_temp where Location like 'ehx%'
select * into cchData_temp_ehe from cchData_temp where Location like 'ehe%'
--GO
select * into cchData_temp_lrx from cchData_temp where Location like 'lrx%'
select * into cchData_temp_lre from cchData_temp where Location like 'lre%'
--GO
select * into cchData_temp_tcx from cchData_temp where Location like 'tcx%'
select * into cchData_temp_tce from cchData_temp where Location like 'tce%'
--GO
select * into cchData_temp_tlx from cchData_temp where Location like 'tlx%'
select * into cchData_temp_tle from cchData_temp where Location like 'tle%'
--GO
select * into cchData_temp_whx from cchData_temp where Location like 'whx%'
select * into cchData_temp_whe from cchData_temp where Location like 'whe%'
--GO-- detail
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'v_cchData_20060901') DROP VIEW v_cchData_20060901
--GOelse
begin
declare @sql varchar(8000)
select @sql='
create view v_cchData_20060901 /****就是这里出的问题****/ --建立视图用动态语句完成
as
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_chx f, cchData_temp_lrx t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_lre f, cchData_temp_che t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_tce f, cchData_temp_ehe t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_whx f, cchData_temp_tlx t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime ''from_time'', f.VehClass ''VehClass'', f.Location ''from_Location'', t.TransDateTime ''to_time'', t.Location ''to_Location'', datediff(ss, f.TransDateTime, t.TransDateTime) ''travel_time'', dbo.within5MinPeriod(t.TransDateTime) ''period'' from cchData_temp_tle f, cchData_temp_whe t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime
'
exec (@sql)
end--GO
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_20060901_detail') DROP TABLE cchData_20060901_detail--GO
SELECT TagID, CONVERT(char(19),from_time,120) 'from_time', CONVERT(char(19),to_time,120) 'to_time', from_Location,to_Location,substring(from_Location,1,2) 'origination',substring(to_Location,1,2) 'destination', travel_time, period,getdate() 'adddate' INTO cchData_20060901_detail FROM v_cchData_20060901
---------sp结束--------------
end
set @date=@date+1
end
------------------------------------
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_chx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_lrx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_lre' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_che' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_tce' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_ehe' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_whx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_tlx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_tle' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_whe' 无效。
Server: Msg 4413, Level 16, State 1, Line 83
由于绑定错误,未能使用视图或函数 'v_cchData_20060901'。
-------------------------------------
另外我希望程序中的v_cchData_20060901 cchData_20060901_detail换成自动更名的,像
cchData_CONVERT(varchar(8),@date,112) cchData_CONVERT(varchar(8),@date,112)_detail,请问如何实现
DECLARE @date datetime
SET @date = '01-9-2006'if (datepart(dw,@date)!=6 AND datepart(dw,@date)!=7 )
begin-----------------------------------
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp') DROP TABLE cchData_temp
--GO
SELECT * INTO cchData_temp FROM tag_Sep2006
WHERE TransDateTime >= @date AND TransDateTime < @date+1
--GO
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_chx') DROP TABLE cchData_temp_chx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_che') DROP TABLE cchData_temp_che
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_ehx') DROP TABLE cchData_temp_ehx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_ehe') DROP TABLE cchData_temp_ehe
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_lrx') DROP TABLE cchData_temp_lrx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_lre') DROP TABLE cchData_temp_lre
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tcx') DROP TABLE cchData_temp_tcx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tce') DROP TABLE cchData_temp_tce
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tlx') DROP TABLE cchData_temp_tlx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_tle') DROP TABLE cchData_temp_tle
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_whx') DROP TABLE cchData_temp_whx
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_temp_whe') DROP TABLE cchData_temp_whe
--GO
select * into cchData_temp_chx from cchData_temp where Location like 'chx%'
select * into cchData_temp_che from cchData_temp where Location like 'che%'
--GO
select * into cchData_temp_ehx from cchData_temp where Location like 'ehx%'
select * into cchData_temp_ehe from cchData_temp where Location like 'ehe%'
--GO
select * into cchData_temp_lrx from cchData_temp where Location like 'lrx%'
select * into cchData_temp_lre from cchData_temp where Location like 'lre%'
--GO
select * into cchData_temp_tcx from cchData_temp where Location like 'tcx%'
select * into cchData_temp_tce from cchData_temp where Location like 'tce%'
--GO
select * into cchData_temp_tlx from cchData_temp where Location like 'tlx%'
select * into cchData_temp_tle from cchData_temp where Location like 'tle%'
--GO
select * into cchData_temp_whx from cchData_temp where Location like 'whx%'
select * into cchData_temp_whe from cchData_temp where Location like 'whe%'
--GO-- detail
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'v_cchData_20060901') DROP VIEW v_cchData_20060901
--GOelse
begin
declare @sql varchar(8000)
set @sql='
create view v_cchData_20060901
as
select f.TagID,f.TransDateTime "from_time", f.VehClass "VehClass", f.Location "from_Location", t.TransDateTime "to_time", t.Location "to_Location", datediff(ss, f.TransDateTime, t.TransDateTime) "travel_time", dbo.within5MinPeriod(t.TransDateTime) "period" from cchData_temp_chx f, cchData_temp_lrx t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime "from_time", f.VehClass "VehClass", f.Location "from_Location", t.TransDateTime "to_time", t.Location "to_Location", datediff(ss, f.TransDateTime, t.TransDateTime) "travel_time", dbo.within5MinPeriod(t.TransDateTime) "period" from cchData_temp_lre f, cchData_temp_che t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime "from_time", f.VehClass "VehClass", f.Location "from_Location", t.TransDateTime "to_time", t.Location "to_Location", datediff(ss, f.TransDateTime, t.TransDateTime) "travel_time", dbo.within5MinPeriod(t.TransDateTime) "period" from cchData_temp_ehx f, cchData_temp_tcx t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime "from_time", f.VehClass "VehClass", f.Location "from_Location", t.TransDateTime "to_time", t.Location "to_Location", datediff(ss, f.TransDateTime, t.TransDateTime) "travel_time", dbo.within5MinPeriod(t.TransDateTime) "period" from cchData_temp_tce f, cchData_temp_ehe t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime "from_time", f.VehClass "VehClass", f.Location "from_Location", t.TransDateTime "to_time", t.Location "to_Location", datediff(ss, f.TransDateTime, t.TransDateTime) "travel_time", dbo.within5MinPeriod(t.TransDateTime) "period" from cchData_temp_whx f, cchData_temp_tlx t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime union all
select f.TagID,f.TransDateTime "from_time", f.VehClass "VehClass", f.Location "from_Location", t.TransDateTime "to_time", t.Location "to_Location", datediff(ss, f.TransDateTime, t.TransDateTime) "travel_time", dbo.within5MinPeriod(t.TransDateTime) "period" from cchData_temp_tle f, cchData_temp_whe t where f.TagID = t.TagID and f.TransDateTime < t.TransDateTime
'
exec (@sql)
end
--GO
IF EXISTS (SELECT NULL FROM sysobjects WHERE name = 'cchData_20060901_detail') DROP TABLE cchData_20060901_detail--GO
SELECT TagID, CONVERT(char(19),from_time,120) 'from_time', CONVERT(char(19),to_time,120) 'to_time', from_Location,to_Location,substring(from_Location,1,2) 'origination',substring(to_Location,1,2) 'destination', travel_time, period,getdate() 'adddate' INTO cchData_20060901_detail FROM v_cchData_20060901
-----------------------
end
老大 看看你那些DROP TABLE ....
把表都删了,并没有Create Table的语句,下面接着就select * into cchData_temp_chx ....
不出错都有鬼了
要改的话,是不是每个都用动态语句?
另外你仔细看一下一楼给你的代码,单撇号 ' 放到动态Sql里之后用两个单撇号''表示,而不是一个双撇号"
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_chx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_lrx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_lre' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_che' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_tce' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_ehe' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_whx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_tlx' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_tle' 无效。
Server: Msg 208, Level 16, State 1, Procedure v_cchData_20060901, Line 4
对象名 'cchData_temp_whe' 无效。应该是表没有create出来