大家帮忙看看我这个存储过程，数据都拷贝结束，而且没有丢失数据，可是总是提示由字符串转换到datetime时发生语法错误

[xxrl]

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

[pbsql]

时间两端加上引号试试：
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'

[zjcxc]

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 Nvarchar(4000) --改定义
declare @sql_ Nvarchar(4000) --改定义

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 sp_executesql @sql_
,N'@tt datetime,@ss smallint,@ll real,@pid integer'
,@tt,@ss,@ll,@pid
fetch next from @lp
into @pid,@tt,@ss,@ll
end
close @lp
deallocate @lp

return 0
GO

[zjcxc]

--问题就在这一句,字符串相加要求都转换成字符型,不然就像我那样改为动态参数传递

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'

[WangZWang]

............
...........
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,'''+cast(@search_date as varchar(20))+''')<=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='++cast(@tt varchar(20))+',s2='++cast(@ss varchar(20))+',l2='++cast(@ll varchar(20))+
' from (select top 1 * from '+@table_name+' where t1 < '+cast(@tt varchar(20))+
' and pid ='+cast(@pid varchar(20))+' order by t1 DESC ) as tx where tx.ID='+@table_name+'.ID'
exec(@sql_)
fetch next from @lp
into @pid,@tt,@ss,@ll
end
...............
........

[老宛]

可能是这个datediff(day,dateid,'+@search_date+')<=1'，你的@search_date是字符型的，可能会没有按照日期格式写，另外，应该用''把@search_date的值括起来,写成
datediff(day,dateid,'''+@search_date+''')<=1'试试

[jiangchuandong]

有可能是你的字符串在转化为datetime类型越界照成的，比如是类似于2004-08-32这样的字符串

[朋友别哭]

up