一个小问题，请求各位大哥帮忙？

[daqi2010]

declare @sql varchar(8000)
declare @sql2 varchar(8000)
declare @year int
set @sql=2009
set @sql2=''
set @sql='
select a.chitty_name as ''name'',
convert(nvarchar,cast(sum(case when datepart(year,a.chitty_addtime) = @year and datepart(month,a.chitty_addtime) = 1 then b.pro_price else 0 end) as money),0)as ''m1'',
convert(nvarchar,cast(sum(case when datepart(year,a.chitty_addtime) = @year and datepart(month,a.chitty_addtime) = 2 then b.pro_price else 0 end) as money),0)as ''m2''
from chitty a left join proitem b on a.chitty_id=b.chitty_id where 1=1 ' +@sql2+ 'group by a.chitty_name'
PRINT( @Sql)
EXECUTE (@SQL)
GO
-----执行提示--------
必须声明变量 '@year'。
请问应该如何写，谢谢大家

[feixianxxx]

declare @sql varchar(8000) 
declare @sql2 varchar(8000) 
declare @year int 
set @sql=2009 
set @sql2='' 
set @sql=' 
select a.chitty_name as ''name'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) =' +rtrim(@year)+ 'and datepart(month,a.chitty_addtime) = 1 then b.pro_price else 0 end)  as  money),0)as ''m1'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) =' +rtrim(@year)+ 'and datepart(month,a.chitty_addtime) = 2 then b.pro_price else 0 end)  as  money),0)as ''m2'' 
from chitty  a left join proitem  b on a.chitty_id=b.chitty_id where 1=1 ' +@sql2+ 'group by a.chitty_name' 
PRINT( @Sql) 
EXECUTE  (@SQL) 
GO 

[nalnait]

[Quote=引用 1 楼 feixianxxx 的回复:]
SQL codedeclare@sqlvarchar(8000)declare@sql2varchar(8000)declare@yearintset@sql=2009set@sql2=''set@sql='
select a.chitty_name as''name'',
convert(nvarchar,cast(sum(case when datepart(year,a.chitty_¡­
[/Quote].

[SQL77]

declare @sql varchar(8000) 
declare @sql2 varchar(8000) 
declare @year int 
set @sql=2009 
set @sql2='' 
set @sql=' 
select a.chitty_name as ''name'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) ='+LTRIM(@year)+' and datepart(month,a.chitty_addtime) = 1 then b.pro_price else 0 end)  as  money),0)as ''m1'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) = '+LTRIM(@year)+'  and datepart(month,a.chitty_addtime) = 2 then b.pro_price else 0 end)  as  money),0)as ''m2'' 
from chitty  a left join proitem  b on a.chitty_id=b.chitty_id where 1=1 ' +@sql2+ 'group by a.chitty_name' 
PRINT( @Sql) 
EXECUTE  (@SQL) 
GO

[SQL77]

declare @sql varchar(8000) 
declare @sql2 varchar(8000) 

declare @year int 
set @year=2009 
set @sql2='' 
set @sql=' 
select a.chitty_name as ''name'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) ='+LTRIM(@year)+' and datepart(month,a.chitty_addtime) = 1 then b.pro_price else 0 end)  as  money),0)as ''m1'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) = '+LTRIM(@year)+'  and datepart(month,a.chitty_addtime) = 2 then b.pro_price else 0 end)  as  money),0)as ''m2'' 
from chitty  a left join proitem  b on a.chitty_id=b.chitty_id where 1=1 ' +@sql2+ 'group by a.chitty_name' 
PRINT @Sql

 
select a.chitty_name as 'name', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) =2009 and datepart(month,a.chitty_addtime) = 1 then b.pro_price else 0 end)  as  money),0)as 'm1', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) = 2009  and datepart(month,a.chitty_addtime) = 2 then b.pro_price else 0 end)  as  money),0)as 'm2' 
from chitty  a left join proitem  b on a.chitty_id=b.chitty_id where 1=1 group by a.chitty_name

[bancxc]

declare @sql varchar(8000) 
declare @sql2 varchar(8000) 
declare @year int 
set @year=2009 -----------
set @sql2='' 
set @sql=' 
select a.chitty_name as ''name'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) =' +rtrim(@year)+ 'and datepart(month,a.chitty_addtime) = 1 then b.pro_price else 0 end)  as  money),0)as ''m1'', 
convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) =' +rtrim(@year)+ 'and datepart(month,a.chitty_addtime) = 2 then b.pro_price else 0 end)  as  money),0)as ''m2'' 
from chitty  a left join proitem  b on a.chitty_id=b.chitty_id where 1=1 ' +@sql2+ 'group by a.chitty_name' 
PRINT( @Sql) 
EXECUTE  (@SQL) 
GO 

[--小F--]

declare @sql varchar(8000) 
declare @sql2 varchar(8000) 

declare @year int 
set @year=2009 
set @sql2='' 
set @sql=' 
select 
   a.chitty_name as ''name'', 
   convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) =
   '+LTRIM(@year)+' and datepart(month,a.chitty_addtime) = 1 
then 
   b.pro_price else 0 end)  as  money),0)as ''m1'', 
   convert(nvarchar,cast(sum(case when  datepart(year,a.chitty_addtime) = 
   '+LTRIM(@year)+'  and datepart(month,a.chitty_addtime) = 2 
then 
  b.pro_price else 0 end)  as  money),0)as ''m2'' 
from 
  chitty  a 
left join proitem  b on 
  a.chitty_id=b.chitty_id 
where 
  1=1 ' +@sql2+ '
group by 
  a.chitty_name' 
EXECUTE (@Sql)
go

[Flyingdragon168]

楼主的SQL语句有问题。
就是这句了。
set @sql='
select a.chitty_name as ''name'',
convert(nvarchar,cast(sum(case when datepart(year,a.chitty_addtime) ='+LTRIM(@year)+' and datepart(month,a.chitty_addtime) = 1 then b.pro_price else 0 end) as money),0)as ''m1'',
convert(nvarchar,cast(sum(case when datepart(year,a.chitty_addtime) = '+LTRIM(@year)+' and datepart(month,a.chitty_addtime) = 2 then b.pro_price else 0 end) as money),0)as ''m2''
from chitty a left join proitem b on a.chitty_id=b.chitty_id where 1=1 ' +@sql2+ 'group by a.chitty_name'

[云中客]

变量 '@year' 没有赋值，不能使用

[daqi2010]

谢谢大家，按照大家的方法，没有数据显示。一片空白

[SQL77]

[Quote=引用 9 楼 daqi2010 的回复:]
谢谢大家，按照大家的方法，没有数据显示。一片空白
[/Quote]
你的@YEAR没赋值,弄错了,我们改了