sp_executesql问题 解决立马结帖

[happy664618843]

declare @DataSizeValue varchar(100)
set @strSQL='select top 1  @DataSizeValue=Sum(CounterValue) from (
			select CounterValue from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterDateTime > (select dateadd(ss,-5,(select MAX(CounterDateTime)
			from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterID in  ('6252')))) and CounterID in  ('6252')
			and InstanceName like ''%''(select top 1 InstanceName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+' )''%''
			and InstanceName like ''%''(select top 1 DBName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+' )''%'' ) T
			' 
			EXEC sp_executesql @strSQL, N'@DataSizeValue varchar(100) output',@DataSizeValue OUTPUT	

主要是在执行 这个sql语句上报错:EXEC sp_executesql @strSQL, N'@DataSizeValue varchar(100) output',@DataSizeValue OUTPUT
错误信息如下:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

[中国风]

e.g.

declare @DataSizeValue nvarchar(4000)
set @strSQL=N'select top 1  @DataSizeValue=Sum(CounterValue) from (
			select CounterValue from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterDateTime > (select dateadd(ss,-5,(select MAX(CounterDateTime)
			from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterID in  (''6252'')))) and CounterID in  (''6252'')
			and InstanceName like ''%''(select top 1 InstanceName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+' )''%''
			and InstanceName like ''%''(select top 1 DBName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+' )''%'' ) T
			' 
			EXEC sp_executesql @strSQL, N'@DataSizeValue varchar(100) output',@DataSizeValue OUTPUT	

[中国风]

sp_executesql--用要unicode字符 @DataSizeValue nvarchar(100) 你的语句中少了引号，用上面代码测测

[happy664618843]

引用 1 楼 roy_88 的回复:

e.g.

declare @DataSizeValue nvarchar(4000)
set @strSQL=N'select top 1  @DataSizeValue=Sum(CounterValue) from (
			select CounterValue from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterDateTime > (select dateadd(ss,-5,(select MAX(CounterDateTime)
			from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterID in  (''6252'')))) and CounterID in  (''6252'')
			and InstanceName like ''%''(select top 1 InstanceName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+' )''%''
			and InstanceName like ''%''(select top 1 DBName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+' )''%'' ) T
			' 
			EXEC sp_executesql @strSQL, N'@DataSizeValue varchar(100) output',@DataSizeValue OUTPUT	

谢谢！ 引号都加了，都按照你的方式改过来，执行EXEC sp_executesql @strSQL, N'@DataSizeValue nvarchar(4000) output',@DataSizeValue OUTPUT 还是报错Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

[happy664618843]

改成这种方式 EXEC sp_executesql @strSQL, N'@DataSizeValue varchar(100) output',@DataSizeValue OUTPUT 执行 都是报一样的错误。

[happy664618843]

补充下：CounterValue在数据库是decimal类型，eg:值为:10.66876

[中国风]

看到了，语句本身还有一堆语法错误

[中国风]

select top 1 @DataSizeValue=Sum(CounterValue) --这类写法那里学的？？完全乱来 top 1取第1条， sum(）--聚集函数只会有一条

[中国风]

用以下代码测测 你的代码不全，接合没贴出来的部份，print 显示语句检查语法 e.g.

declare @DataSizeValue nvarchar(4000)
set @strSQL=N'DECLARE @InstanceName NVARCHAR(1000),@DBName NVARCHAR(1000);
select top 1 @InstanceName=InstanceName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+';
select top 1 @DBName=DBName  from #ASB_SaveInstanceInfo where ID='+cast(@j as varchar)+';
select @DataSizeValue=SUM(CounterValue) 
FROM dbo.ASBSQL_PerformanceCounterDetailM7
where CounterDateTime > (select dateadd(ss,-5,(select MAX(CounterDateTime)
from dbo.ASBSQL_PerformanceCounterDetailM7
where CounterID in  (''6252'')))) and CounterID in  (''6252'')
and InstanceName like ''%''+@InstanceName+''%''
and InstanceName like ''%''+@DBName+''%''' 
EXEC sp_executesql @strSQL, N'@DataSizeValue varchar(100) output',@DataSizeValue OUTPUT	

[xiaoxiangqing]

declare @DataSizeValue varchar(100)-->改为declare @DataSizeValue nvarchar(100)

[happy664618843]

谢谢roy_88. 原sql执行后打印出来的sql语句如下:

select   @DataSizeValue=Sum(CounterValue) from (
			select CounterValue from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterDateTime > (select dateadd(ss,-5,(select MAX(CounterDateTime)
			from dbo.ASBSQL_PerformanceCounterDetailM7
			where CounterID in  ('6255')))) and CounterID in  ('6255')
			and InstanceName like '%'(select top 1 InstanceName  from #ASB_SaveInstanceInfo where ID=1 )'%'
			and InstanceName like '%'(select top 1 DBName  from #ASB_SaveInstanceInfo where ID=1 )'%' ) T