存储过程中提示列名无效的问题，百思不得其解，特来求解？

[codeangel]

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'无效”

[云中客]

select @x= sum([@p_ItemName]) FROM Cost_Dyfy_x
这句的问题
你可以这样来实现
Declare @Sql1 NVarchar(1000)
set @Sql1="select @x= sum([" + @p_ItemName] + ") FROM Cost_Dyfy_x"
exec(@sql1)

[liangpei2008]

select @x= sum([@p_ItemName]) FROM Cost_Dyfy_x --执行到这里老提示“列名'@p_ItemName'无效”--应该使用动态SQL

[liangpei2008]

楼上大哥快啊:)

[bugchen888]

列名不能用变量。如果列名确实是变化的，只能写成动态SQL。
如：
@sql='select sum([' + @p_ItemName + ']) from Cost_Dyfy_x'

exec (@sql)

[codeangel]

按照云中客的想法
提示我
-------------------------------------
服务器: 消息 137，级别 15，状态 1，行 1
必须声明变量 '@x'。
-------------------------------------
我明明已经声明了啊

[liangpei2008]

Declare @Sql1 NVarchar(1000)
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

[codeangel]

bugchen888(臭虫)
我想动态取出这些sum(字段)值，该怎么做?

[codeangel]

谢谢，我爱世界杯

[云中客]

Declare @Sql1 Varchar(1000)
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

[云中客]

在这里用exec命令不可以，必须用系统存储过程sp_executesql

[codeangel]

我试试

[codeangel]

CREATE 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 @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))+')'

[codeangel]

谢谢各位，已搞定。结帖

[junshanhudazhaxi]

哪里错了，你还没补充呢！！！！这样可不道德哦。