为啥子出错！！

[HungryBoy]

CREATE PROCEDURE pro_AcMode_CallIn_View(@dYear int)
AS


declare @sql varchar(8000)
set @sql = 'select Acmode'
select @sql = @sql + ',sum(case AcSection when '''+AcSection+''' then Pass else 0 end) as ['+AcSection+'/Pass],sum(case AcSection when '''+AcSection+''' then ManNum else 0 end) as ['+AcSection+'/ManNum]'
from (select distinct AcSection from C_OutSide_AcMode_CallIn_View) as a
select @sql = @sql+' from C_OutSide_AcMode_CallIn_View where Year=@dYear group by AcMode'

exec(@sql)
GO



服务器: 消息 137，级别 15，状态 2，行 1
必须声明变量 '@dYear'。

[HungryBoy]

???

[pengdali]

exec ('select * from '+@TableView)

[HungryBoy]

CREATE PROCEDURE pro_AcMode_CallIn_View(@TableView varchar(200),@dYear int)
AS

--C_OutSide_AcMode_CallIn_View
declare @sql varchar(8000)
set @sql = 'select Acmode'
select @sql = @sql + ',sum(case AcSection when '''+AcSection+''' then Pass else 0 end) as ['+AcSection+'/Pass],sum(case AcSection when '''+AcSection+''' then ManNum else 0 end) as ['+AcSection+'/ManNum]'
from exec('select distinct AcSection from '+@TableView +' where Year=cast(@dYear as varchar)' ) as a
select @sql = @sql+' from '+@TableView+' where Year='+cast(@dYear as varchar)+' group by AcMode'

exec(@sql)
GO

是这样吗？

还是出错阿！！：（（

[HungryBoy]

？？？

[shengyh]

from 从哪个表中啊？？？

[HungryBoy]

是传过来的参数：@TableView

[HungryBoy]

??

[vbasten]

没有问题
你再试试

[zjcxc]

当然出错啦,你不能将@dYear变量直接带入到@SQL语句中,使用exec(@sql)执行时,相当于@sql为另一个过程,当然不能使用@dYear变量,改为下面的语句就行啦

CREATE PROCEDURE pro_AcMode_CallIn_View(@dYear int)
AS


declare @sql varchar(8000)
set @sql = 'select Acmode'
select @sql = @sql + ',sum(case AcSection when '''+AcSection+''' then Pass else 0 end) as ['+AcSection+'/Pass],sum(case AcSection when '''+AcSection+''' then ManNum else 0 end) as ['+AcSection+'/ManNum]'
from (select distinct AcSection from C_OutSide_AcMode_CallIn_View) as a
select @sql = @sql+' from C_OutSide_AcMode_CallIn_View where Year='+ convert(varchar(10),@dYear)+' group by AcMode'

exec(@sql)
GO

[pengdali]

CREATE PROCEDURE pro_AcMode_CallIn_View(@dYear int)
AS


declare @sql varchar(8000)
set @sql = 'select Acmode'
select @sql = @sql + ',sum(case AcSection when '''+AcSection+''' then Pass else 0 end) as ['+AcSection+'/Pass],sum(case AcSection when '''+AcSection+''' then ManNum else 0 end) as ['+AcSection+'/ManNum]'
from (select distinct AcSection from C_OutSide_AcMode_CallIn_View) as a
select @sql = @sql+' from C_OutSide_AcMode_CallIn_View where Year='''+@dYear+''' group by AcMode'

exec(@sql)
GO

[pengdali]

CREATE PROCEDURE pro_AcMode_CallIn_View(@dYear int)
AS


declare @sql varchar(8000)
set @sql = 'select Acmode'
select @sql = @sql + ',sum(case AcSection when '''+AcSection+''' then Pass else 0 end) as ['+AcSection+'/Pass],sum(case AcSection when '''+AcSection+''' then ManNum else 0 end) as ['+AcSection+'/ManNum]'
from (select distinct AcSection from C_OutSide_AcMode_CallIn_View) as a
select @sql = @sql+' from C_OutSide_AcMode_CallIn_View where Year='+cast(@dYear as varchar)+' group by AcMode'

exec(@sql)
GO

[HungryBoy]

出现了新的错误：
服务器: 消息 245，级别 16，状态 1，过程 pro_AcMode_CallIn_View，行 9
将 varchar 值 'select Acmode,sum(case AcSection when '国际处' then Pass else 0 end) as [国际处/Pass],sum(case AcSection when '国际处' then ManNum else 0 end) as [国际处/ManNum],sum(case AcSection when '联络处' then Pass else 0 end) as [联络处/Pass],sum(case AcSection when '联络处' then ManNum else 0 end) as [联络处/ManNum],sum(case AcSection when '欧洲处' then Pass else 0 end) as [欧洲处/Pass],sum(case AcSection when '欧洲处' t...

[zjcxc]

检查一下,你的sum中包含的字段类型是否是数字,如果不是的话,那用数据转换
贴出你的数据结构和你现在的存储过程语句.

[HungryBoy]

谢谢上个问题已解决！！

CREATE PROCEDURE pro_AcMode_CallIn_View(@TableView varchar(200),@dYear int)

我想把C_OutSide_AcMode_CallIn_View 用变量@TableView怎么替换阿！