拼接SQL的问题

[月光下的土豆]

Create proc GetItemCount
	@itemID int,
	@yearStr nvarchar(40),
	@deptStr nvarchar(40)
as
	declare @w nvarchar(1000)
	declare @Sql nvarchar(2000)
	if LEN(@deptStr)=0
		Set @w = ''
	else
		Set @w = ' and YearPlan_UseDept = '''+@deptStr+''''
	Set @Sql = 'Select SUM(YearPlan_Amount),SUM(YearPlan_ly),SUM(YearPlan_ls),SUM(YearPlan_lg) From	Mpt_YearPlan Where YearPlan_Year = '''+@yearStr+'''  and YearPlan_ItemSmlID = '+ @itemID + @w
	print(@Sql)
Go
exec GetItemCount 12,'df','bm'

提示
消息 245，级别 16，状态 1，过程 GetItemCount，第 13 行
在将 nvarchar 值 'Select SUM(YearPlan_Amount),SUM(YearPlan_ly),SUM(YearPlan_ls),SUM(YearPlan_lg) From Mpt_YearPlan Where YearPlan_Year = 'df' and YearPlan_ItemSmlID = ' 转换成数据类型 int 时失败。

[hao1hao2hao3]

try

 Set @Sql = 'Select SUM(YearPlan_Amount),SUM(YearPlan_ly),SUM(YearPlan_ls),SUM(YearPlan_lg) From    Mpt_YearPlan Where YearPlan_Year = '''+@yearStr+'''  and YearPlan_ItemSmlID = '+ cast(@itemID as varchar(10)) + @w

[chen8410]

exec GetItemCount 12,'df','bm'
第二个参数不能是字母（'df'），只能是int型数字

[nice_dream_li]

最后的这个 @itemID + @w 有问题，因为@itemid  int 型的，
所以要转换一下 用ltrim(@itemid)或者 CAST 都可以

[nice_dream_li]

---结果
Select SUM(YearPlan_Amount),SUM(YearPlan_ly),SUM(YearPlan_ls),SUM(YearPlan_lg) 
    From Mpt_YearPlan Where YearPlan_Year = 'df'  
    and YearPlan_ItemSmlID = 12 and YearPlan_UseDept = 'bm'

[幸运的意外]

Create proc GetItemCount
@itemID int,
@yearStr nvarchar(40),
@deptStr nvarchar(40)
as
declare @w nvarchar(1000)
declare @Sql nvarchar(2000)
if LEN(@deptStr)=0
Set @w = ''
else
Set @w = ' and YearPlan_UseDept = '''+@deptStr+''''
Set @Sql = 'Select SUM(YearPlan_Amount),SUM(YearPlan_ly),SUM(YearPlan_ls),SUM(YearPlan_lg) From Mpt_YearPlan Where YearPlan_Year = '''+@yearStr+''' and YearPlan_ItemSmlID = '+ cast（@itemID as varchar(10)) + @w
print(@Sql)
Go
exec GetItemCount 12,'df','bm'

[月光下的土豆]

晕。结错了。一楼的分给二楼了。
下次多补给你