sp_executesql问题 解决立马结帖

happy664618843 2016-12-13 09:26:07

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'.
...全文
219 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
happy664618843 2016-12-13
  • 打赏
  • 举报
回复
谢谢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
xiaoxiangqing 2016-12-13
  • 打赏
  • 举报
回复
declare @DataSizeValue varchar(100)-->改为declare @DataSizeValue nvarchar(100)
中国风 2016-12-13
  • 打赏
  • 举报
回复
用以下代码测测 你的代码不全,接合没贴出来的部份,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	
中国风 2016-12-13
  • 打赏
  • 举报
回复
select top 1 @DataSizeValue=Sum(CounterValue) --这类写法那里学的??完全乱来 top 1取第1条, sum()--聚集函数只会有一条
中国风 2016-12-13
  • 打赏
  • 举报
回复
看到了,语句本身还有一堆语法错误
happy664618843 2016-12-13
  • 打赏
  • 举报
回复
补充下:CounterValue在数据库是decimal类型,eg:值为:10.66876
happy664618843 2016-12-13
  • 打赏
  • 举报
回复
改成这种方式 EXEC sp_executesql @strSQL, N'@DataSizeValue varchar(100) output',@DataSizeValue OUTPUT 执行 都是报一样的错误。
happy664618843 2016-12-13
  • 打赏
  • 举报
回复
引用 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'.
中国风 2016-12-13
  • 打赏
  • 举报
回复
sp_executesql--用要unicode字符 @DataSizeValue nvarchar(100) 你的语句中少了引号,用上面代码测测
中国风 2016-12-13
  • 打赏
  • 举报
回复
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	

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧