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
uj5u.com熱心網友回復：
/*
首先你的@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
uj5u.com熱心網友回復：
--我插,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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/213113.html
標籤：基礎類
上一篇：sql 分類匯總