存储过程数据类型从nvarchar转换为float出错

[csdn2020114]

ALTER proc [dbo].[addbktodaydata1]
@bankuaicode varchar(50)
as
declare @sql nvarchar(1000)
declare @sql1 nvarchar(1000)
declare @sql2 nvarchar(1000)
declare @bankuainame nvarchar(100)
declare @begintime1 nvarchar(20)
declare @sum5 float
begin
set @begintime1=convert(varchar(10),getdate(),120)
set @bankuainame='zhy'+@bankuaicode
set @sql='insert into '+@bankuainame+' (begintime,bankuaicode,dayopen,dayhigh,daylow,dayclose,dayvol) select begintime,bankuaicode,dayopen,dayhigh,daylow,dayclose,dayvol from lsbankuaidata where bankuaicode='+@bankuaicode+'and begintime='+@begintime1+''
exec(@sql)
set @sql1='select @sum5=sum(t.dayclose) from (select top 5 dayclose from '+@bankuainame+' where begintime= '+@begintime1+ ' order by begintime desc) t'
exec sp_executesql @sql1,N'@sum5 float',@sum5
set @sql2='update' +@bankuainame+ 'set day5= '+@sum5+' where begintime='+@begintime1+''
exec (@sql2)
end

[雨夹雪]

/*
首先你的@sum5是float类型,你跟字符串拼在一起的时候,不转换,这样报nvarchar转换为float出错
其次,你的查询语句少了单引号,你打印出来会看到是这样:
SELECT begintime,bankuaicode,dayopen,dayhigh,daylow,dayclose,dayvol from lsbankuaidata where bankuaicode=值 and begintime=2020-11-12
再次,你的bankuaicode和begintime两个条件直接的and这里少了一个空格
再再次,几计算@sum5,你sp_executesql的时候没有用output,这样你是得不到字符串里面计算的结果的
*/	
ALTER PROC [dbo].[addbktodaydata1]
@bankuaicode VARCHAR(50)
AS
BEGIN
	DECLARE @sql NVARCHAR(1000)
	DECLARE @sql1 NVARCHAR(1000)
	DECLARE @sql2 NVARCHAR(1000)
	DECLARE @bankuainame NVARCHAR(100)
	DECLARE @begintime1 NVARCHAR(20)
	DECLARE @sum5 float
	 
	SET @begintime1=CONVERT(VARCHAR(10),GETDATE(),120)
	SET @bankuainame='zhy'+@bankuaicode
	SET @sql='insert into '+@bankuainame+' (begintime,bankuaicode,dayopen,dayhigh,daylow,dayclose,dayvol) 
	select begintime,bankuaicode,dayopen,dayhigh,daylow,dayclose,dayvol 
	from lsbankuaidata where bankuaicode=@bankuaicode and begintime=@begintime1'
	EXEC sp_executesql @sql,N'@bankuaicode VARCHAR(50),@begintime1,NVARCHAR(20)', @bankuaicode,@begintime1
	
	SET @sql1='select @sum5=sum(t.dayclose) from (select top 5 dayclose from '+@bankuainame+' where begintime= @begintime1 order by begintime desc) t'
	exec sp_executesql @sql1,N'@begintime1 NVARCHAR(20),@sum5 float output',@begintime1,@sum5 OUTPUT
	
	SET @sql2='update' +@bankuainame+ 'SET day5= @sum5 where begintime=@begintime1'
	
	exec sp_executesql @sql2,N'@begintime1 NVARCHAR(20),@sum5 float',@begintime1,@sum5
END

[雨夹雪]

--我插,update 表和set之前也少了一个空格
ALTER PROC [dbo].[addbktodaydata1]
@bankuaicode VARCHAR(50)
AS
BEGIN
    DECLARE @sql NVARCHAR(1000)
    DECLARE @sql1 NVARCHAR(1000)
    DECLARE @sql2 NVARCHAR(1000)
    DECLARE @bankuainame NVARCHAR(100)
    DECLARE @begintime1 NVARCHAR(20)
    DECLARE @sum5 float
      
    SET @begintime1=CONVERT(VARCHAR(10),GETDATE(),120)
    SET @bankuainame='zhy'+@bankuaicode
    SET @sql='insert into '+@bankuainame+' (begintime,bankuaicode,dayopen,dayhigh,daylow,dayclose,dayvol) 
    select begintime,bankuaicode,dayopen,dayhigh,daylow,dayclose,dayvol 
    from lsbankuaidata where bankuaicode=@bankuaicode and begintime=@begintime1'
    EXEC sp_executesql @sql,N'@bankuaicode VARCHAR(50),@begintime1,NVARCHAR(20)', @bankuaicode,@begintime1
     
    SET @sql1='select @sum5=sum(t.dayclose) from (select top 5 dayclose from '+@bankuainame+' where begintime= @begintime1 order by begintime desc) t'
    exec sp_executesql @sql1,N'@begintime1 NVARCHAR(20),@sum5 float output',@begintime1,@sum5 OUTPUT
     
    SET @sql2='update' +@bankuainame+ ' SET day5= @sum5 where begintime=@begintime1'
     
    exec sp_executesql @sql2,N'@begintime1 NVARCHAR(20),@sum5 float',@begintime1,@sum5
END

[xiaoxiangqing]

需要保证能够转换才行

[卖水果的net]

set @sql2='update' +@bankuainame+ 'set day5= '+@sum5+' where begintime='+@begintime1+'' exec (@sql2) 实际上，这里才是报错的地方, 改成如下： set @sql2='update' +@bankuainame+ 'set day5= '+ltrim(@sum5)+' where begintime='+@begintime1+'' exec (@sql2)

[csdn2020114]

刚看到你们的回贴,,不胜感激,C#水平有限,我调试一下,结分