REATE PROCEDURE SP_FillDataForDyfy
(
@Year VarChar(4)--年度
) AS
DECLARE @Sql NVarchar(1000)
DECLARE @x decimal(18,2)
DECLARE @y decimal(18,2)
DECLARE @Dyczf_x decimal(18,2)
DECLARE @Dyczf_y decimal(18,2)--生成之前删除原有数据select @Dyczf_x=sum([原油商品量(吨)]) FROM Cost_Dyfy_x
select @Dyczf_y=sum([原油商品量(吨)]) FROM Cost_Dyfy_y
print @Dyczf_x
print @Dyczf_y
Declare CSR_Fyfy cursor for select ItemID,ItemName from ItemCost
Open CSR_Fyfy Declare @p_ItemCode varchar(20)
Declare @p_ItemName varchar(100) fetch next from CSR_Fyfy into @p_ItemCode,@p_ItemName
while (@@fetch_status=0)
begin
select @x= sum([@p_ItemName]) FROM Cost_Dyfy_x --执行到这里老提示“列名'@p_ItemName'无效”
print @x select @Sql='insert into Cost_Dyfy (ItemCode,ItemName,Price_x,Dyzcf_x,Price_y,Dyczf_y)values('+Cast(@x AS nvarchar(30)) +')'
print @Sql
exec(@Sql)
fetch next from CSR_Fyfy into @p_ItemCode,@p_ItemName
End Close CSR_Fyfy
Deallocate CSR_Fyfy
SET NOCOUNT OFF
GO
------------------------------------------
select @x= sum([@p_ItemName]) FROM Cost_Dyfy_x --执行到这里老提示“列名'@p_ItemName'无效”
(
@Year VarChar(4)--年度
) AS
DECLARE @Sql NVarchar(1000)
DECLARE @x decimal(18,2)
DECLARE @y decimal(18,2)
DECLARE @Dyczf_x decimal(18,2)
DECLARE @Dyczf_y decimal(18,2)--生成之前删除原有数据select @Dyczf_x=sum([原油商品量(吨)]) FROM Cost_Dyfy_x
select @Dyczf_y=sum([原油商品量(吨)]) FROM Cost_Dyfy_y
print @Dyczf_x
print @Dyczf_y
Declare CSR_Fyfy cursor for select ItemID,ItemName from ItemCost
Open CSR_Fyfy Declare @p_ItemCode varchar(20)
Declare @p_ItemName varchar(100) fetch next from CSR_Fyfy into @p_ItemCode,@p_ItemName
while (@@fetch_status=0)
begin
select @x= sum([@p_ItemName]) FROM Cost_Dyfy_x --执行到这里老提示“列名'@p_ItemName'无效”
print @x select @Sql='insert into Cost_Dyfy (ItemCode,ItemName,Price_x,Dyzcf_x,Price_y,Dyczf_y)values('+Cast(@x AS nvarchar(30)) +')'
print @Sql
exec(@Sql)
fetch next from CSR_Fyfy into @p_ItemCode,@p_ItemName
End Close CSR_Fyfy
Deallocate CSR_Fyfy
SET NOCOUNT OFF
GO
------------------------------------------
select @x= sum([@p_ItemName]) FROM Cost_Dyfy_x --执行到这里老提示“列名'@p_ItemName'无效”
这句的问题
你可以这样来实现
Declare @Sql1 NVarchar(1000)
set @Sql1="select @x= sum([" + @p_ItemName] + ") FROM Cost_Dyfy_x"
exec(@sql1)
如:
@sql='select sum([' + @p_ItemName + ']) from Cost_Dyfy_x'exec (@sql)
Declare @x Numeric
Set @Sql1=N'select @x= sum([' + @p_ItemName+']) FROM Cost_Dyfy_x'
Exec sp_executesql @sql1,N'@x Numeric Output',@x Output
Select @x
我想动态取出这些sum(字段)值,该怎么做?
declare @x int
set @Sql1='select sum([' + @p_ItemName + ']) FROM Cost_Dyfy_x'
select @sql1
EXECUTE sp_executesql @sql1,N'@X int OutPut',@x output
( @Year VarChar(4)--年度,
) AS
DECLARE @Sql NVarchar(1000)
DECLARE @x decimal(18,2)
DECLARE @y decimal(18,2)
DECLARE @Dyczf_x decimal(18,2)
DECLARE @Dyczf_y decimal(18,2)--生成之前删除原有数据select @Dyczf_x=sum([原油商品量(吨)]) FROM Cost_Dyfy_x
select @Dyczf_y=sum([原油商品量(吨)]) FROM Cost_Dyfy_y
print @Dyczf_x
print @Dyczf_y
Declare CSR_Fyfy cursor for select ItemID,ItemName from ItemCost
Open CSR_Fyfy Declare @p_ItemCode varchar(20)
Declare @p_ItemName varchar(100) fetch next from CSR_Fyfy into @p_ItemCode,@p_ItemName
while (@@fetch_status=0)
begin
select @Sql='select sum([' + @p_ItemName + ']) FROM Cost_Dyfy_x'
exec sp_executesql @Sql,N'@x decimal(18,2) output',@x output
select @x select @Sql='select sum(['+@p_ItemName+']) FROM Cost_Dyfy_y '
exec sp_executesql @Sql,N'@y decimal(18,2) output',@y output
select @y
select @Sql='insert into Cost_Dyfy (ItemCode,ItemName,Price_x,Dyzcf_x,Price_y,Dyczf_y)values('+@p_ItemCode+','+@p_ItemName+','+Cast(@x AS nvarchar(30)) +','+Cast(@x/@Dyczf_x AS nvarchar(30))+','+Cast(@y AS nvarchar(30))+','+Cast(@y/@Dyczf_y AS nvarchar(30))+')'
print @Sql
exec(@Sql)
fetch next from CSR_Fyfy into @p_ItemCode,@p_ItemName
end Close CSR_Fyfy
Deallocate CSR_Fyfy
SET NOCOUNT OFF
GO
------------------------------------------------------------
还是不行啊,用执行出来exec sp_executesql @Sql,N'@x decimal(18,2) output',@x output
后不往下执行呢?
为什么不执行将结果插入表中呢?
select @Sql='insert into Cost_Dyfy (ItemCode,ItemName,Price_x,Dyzcf_x,Price_y,Dyczf_y)values('+@p_ItemCode+','+@p_ItemName+','+Cast(@x AS nvarchar(30)) +','+Cast(@x/@Dyczf_x AS nvarchar(30))+','+Cast(@y AS nvarchar(30))+','+Cast(@y/@Dyczf_y AS nvarchar(30))+')'