大家帮忙看看我这个存储过程,数据都拷贝结束,而且没有丢失数据,可是总是提示由字符串转换到datetime时发生语法错误
xxrl 2004-08-11 04:43:57
CREATE PROCEDURE level_ @search_date varchar(50),@table_name varchar(20) AS
declare @tt datetime
declare @lp cursor
declare @pid integer
declare @ss smallint
declare @id integer
declare @ll real
declare @tnow varchar(50)
declare @sql varchar(300)
declare @sql_ varchar(300)
set @tnow =@search_date
set @sql = 'insert into '+@table_name+'(pid,t1,l1,s1) select msgid,dateid,lev,ist from TMSG where ist =1 and datediff(day,dateid,'+@search_date+')<=1'
exec(@sql)
set @lp = cursor for
select msgid,dateid,ist,lev from TMSG
where ist=2 and datediff(DAY,dateid,@tnow)<=1
open @lp
fetch next from @lp
into @pid,@tt,@ss,@ll
while (@@fetch_status=0)
begin
set @sql_ = 'update '+
@table_name+' set t2='+@tt+',s2='+@ss+',l2='+@ll+' from (select top 1 * from '+@table_name+' where t1 < '+@tt+' and pid ='+@pid+' order by t1 DESC ) as tx where tx.ID='+@table_name+'.ID'
exec(@sql_)
fetch next from @lp
into @pid,@tt,@ss,@ll
end
close @lp
deallocate @lp
return 0
GO
-----数据没有丢失
-----报错信息如下
---(所影响的行数为 16198 行)
---服务器: 消息 241,级别 16,状态 1,过程 level_,行 29
---从字符串转换为 datetime 时发生语法错误。
---肯定各位能帮我看看,找一找错误
数据库字段定义如下,是通过存储过程建立的
CREATE PROCEDURE create_year_table AS
declare @t int
declare @table_name varchar(20)
declare @sql varchar(500)
set @t = datepart(year,getdate())
set @table_name = convert(varchar(50),'level_compare_')+convert(varchar(20),@t)
if not exists (select * from sysobjects where id = object_id(@table_name) and OBJECTPROPERTY(id,' IsUserTable') = 1)
begin
set @sql = 'CREATE TABLE '+@table_name +'(
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PID] [int] NOT NULL ,
[T1] [datetime] NOT NULL ,
[L1] [real] NULL ,
[S1] [smallint] NULL ,
[T2] [datetime] NULL ,
[L2] [real] NULL ,
[S2] [smallint] NULL ,
[tD] [real] NULL ,
[tM] [real] NULL ,
[tY] [real] NULL
) ON [PRIMARY]'
exec(@sql)
end
GO