导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

csdn2020114 2020-11-12 02:21:09
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
...全文
210 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
csdn2020114 2020-11-19
刚看到你们的回贴,,不胜感激,C#水平有限,我调试一下,结分
回复
卖水果的net 版主 2020-11-13
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)
回复
xiaoxiangqing 2020-11-13
需要保证能够转换才行
回复
雨夹雪 2020-11-12

--我插,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

回复
雨夹雪 2020-11-12

/*
首先你的@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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告