34,590
社区成员
发帖
与我相关
我的任务
分享SQL 变量问题 解决立马解帖
declare @NowMonth int,@CounterName varchar(100)
set @NowMonth=month(GETDATE())
insert into #ASB_SQLCountValueInfo(BlockedCounterID,BlockedCounterDateTime,BlockedCounterValue,ServerName)
select CounterID,CounterDateTime, CounterValue,‘cnjb01 ’ from dbo.ASBSQL_PerformanceCounterDetailM+'+@NowMonth+' T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName=‘cnjb01 ’ and CounterName='Processes blocked'
)
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<=30
order by CounterDateTime desc
一直报错，咱拼接@NowMonth变量？
拼接完成表名是 dbo.ASBSQL_PerformanceCounterDetailM6
6数字是取当前月份。
...全文
请发表友善的回复…
发表回复
道素 2016-08-05
- 打赏
- 举报
另一种思路，我没有测试
用outer apply代替in和exists
declare @NowMonth int
declare @CounterName varchar(20)
declare @serverName varchar(500)
declare @AlterIntervalTime int
declare @strSql varchar(500)
select @serverName='cnshjssql14.ad4.ad.alcatel.com'
set @CounterName='Processes blocked'
set @AlterIntervalTime=30
set @NowMonth=month(GETDATE())
set @strSql=' select CounterID,CounterDateTime, CounterValue,'''+@serverName+''' as serverName from dbo.ASBSQL_PerformanceCounterDetailM+'+CAST(@NowMonth as varchar)+' T
outer apply (
select top 1 CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList] AS p
where p.ServerName='''+@serverName+''' and p.CounterName=''Processes blocked'' and p.CounterID=T.CounterID
) a Where a.CounterID is NULL
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())>='+ cast(@AlterIntervalTime as varchar)+'
order by CounterDateTime desc'
exec (@strSql)
屎涂行者 2016-08-05
- 打赏
- 举报
declare @NowMonth varchar(10),@CounterName varchar(100)
set @NowMonth=month(GETDATE());
insert into #ASB_SQLCountValueInfo(BlockedCounterID,BlockedCounterDateTime,BlockedCounterValue,ServerName)
select CounterID,CounterDateTime, CounterValue,'cnjb01' from dbo.ASBSQL_PerformanceCounterDetailM+@NowMonth T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName='cnjb01' and CounterName='Processes blocked'
)
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<=30
order by CounterDateTime desc
happy664618843 2016-08-04
- 打赏
- 举报
谢谢你 帮我解决问题
中国风 2016-08-04
- 打赏
- 举报
另：你非要用if exists可这样改
declare @NowMonth int
set @NowMonth=6
declare @AlterIntervalTime int
set @AlterIntervalTime=30
declare @Flag bit=0
declare @strSql nvarchar(1000)
set @strSql=' if exists(select 1 from ASBSQL_PerformanceCounterDetailM'+ CAST(@NowMonth as varchar)+' T where exists(
select 1 from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where CounterName=''Processes blocked'' and CounterID=T.CounterID
)
and CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<='+ cast(@AlterIntervalTime as varchar)+'
)
set @Flag=1
else
set @Flag=0'
EXEC sp_executesql @strSql, N'@Flag BIT output',@Flag OUTPUT
中国风 2016-08-04
- 打赏
- 举报
·又不按上面方法来，把 declare @NowMonth varchar(2)
不需要转换，要转换就要用变量赋值
happy664618843 2016-08-04
- 打赏
- 举报
谢谢！
declare @NowMonth int
set @NowMonth=month(GETDATE())
insert into #ASB_SQLCountValueInfo(UserConnectionCounterID,UserConnectionDateTime,UserConnectionCounterValue,ServerName)
exec( 'select CounterID,CounterDateTime, CounterValue,'''+@serverName+''' from dbo.ASBSQL_PerformanceCounterDetailM'+ CAST(@NowMonth as varchar)+' T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName='''+CAST(@serverName as varchar)+''' and CounterName=''User Connections'')
and T.CounterValue>0 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<='+ cast(@AlterIntervalTime as varchar)+'
order by CounterDateTime desc')
@AlterIntervalTime声明为int 类型 和@serverName 声明为varchar类型
报这句话错误 '+ CAST(@NowMonth as varchar)+'
'CAST' 附近有语法错误。
不能这样转换吗？
中国风 2016-08-04
- 打赏
- 举报
别乱改语句啊
参照#15
没有if exists...
happy664618843 2016-08-04
- 打赏
- 举报
declare @NowMonth int
set @NowMonth=6
declare @AlterIntervalTime int
set @AlterIntervalTime=30
declare @Flag bit=0
declare @strSql nvarchar(800)
set @strSql=' if exists(select TOP 1 @Flag=1 from ASBSQL_PerformanceCounterDetailM'+ CAST(@NowMonth as varchar)+' T where exists(
select 1 from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where CounterName=''Processes blocked'' and CounterID=T.CounterID
)
and CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<='+ cast(@AlterIntervalTime as varchar)+'
)'
EXEC sp_executesql @strSql, N'@Flag BIT output',@Flag OUTPUT
@中国风 非常感谢，但还有错误
消息 102，级别 15，状态 1，第 1 行
'=' 附近有语法错误。
消息 156，级别 15，状态 1，第 5 行
关键字 'and' 附近有语法错误。
中国风 2016-08-04
- 打赏
- 举报
这样判断不行
需要动态去处理
定义变量 改
declare @strSql nvarchar(500)
declare @strSql nvarchar(500)
DECLARE @Flag BIT=0
SET @strSql=' select TOP 1 @Flag=1 from ASBSQL_PerformanceCounterDetailM'+ CAST(@NowMonth as varchar)+' T where exists(
select 1 from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where CounterName=''Processes blocked'' and CounterID=T.CounterID
)
and CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())>='+ cast(@AlterIntervalTime as varchar)+'
'
EXEC sp_executesql @strSql, N'@Flag BIT output',@Flag OUTPUT
BEGIN
IF @Flag=1
INSERT into tbname....
END
happy664618843 2016-08-04
- 打赏
- 举报
@中国风 谢谢！
请教下
set @strSql=' if exists(select 1 from ASBSQL_PerformanceCounterDetailM'+ CAST(@NowMonth as varchar)+' T where exists(
select 1 from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where CounterName=''Processes blocked'' and CounterID=T.CounterID
)
and CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())>='+ cast(@AlterIntervalTime as varchar)+'
)'
exec (@strSql)
begin
insert into tbname....
end
这样可以吗？ 我想判断表中是否有满足的条件再做插入
Ginnnnnnnn 2016-08-04
- 打赏
- 举报
declare @NowMonth int,@CounterName varchar(100)
set @NowMonth=month(GETDATE())
DECLARE @Sql NVARCHAR(max) = '
select CounterID,CounterDateTime, CounterValue,''cnjb01'' from dbo.ASBSQL_PerformanceCounterDetailM'+@NowMonth+' T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName=''cnjb01'' and CounterName=''Processes blocked''
)
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<=30
order by CounterDateTime '
insert into #ASB_SQLCountValueInfo(BlockedCounterID,BlockedCounterDateTime,BlockedCounterValue,ServerName)
EXEC @Sql
中国风 2016-08-04
- 打赏
- 举报
少了引号
declare @NowMonth int
declare @CounterName varchar(20)
declare @serverName varchar(500)
declare @AlterIntervalTime int
declare @strSql varchar(500)
select @serverName='cnshjssql14.ad4.ad.alcatel.com'
set @CounterName='Processes blocked'
set @AlterIntervalTime=30
set @NowMonth=month(GETDATE())
set @strSql='SELECT CounterID,CounterDateTime, CounterValue from dbo.ASBSQL_PerformanceCounterDetailM'+ CAST(@NowMonth as varchar)+' T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName='''+CAST(@serverName as varchar)+''' and CounterName='''+@CounterName+'''
)
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<='+ cast(@AlterIntervalTime as varchar)+'
order by CounterDateTime DESC'
exec (@strSql)
happy664618843 2016-08-04
- 打赏
- 举报
declare @NowMonth int
declare @CounterName varchar(20)
declare @serverName varchar(500)
declare @AlterIntervalTime int
declare @strSql varchar(500)
select @serverName='cnshjssql14.ad4.ad.alcatel.com'
set @CounterName='Processes blocked'
set @AlterIntervalTime=30
set @NowMonth=month(GETDATE())
set @strSql='SELECT CounterID,CounterDateTime, CounterValue from dbo.ASBSQL_PerformanceCounterDetailM'+ CAST(@NowMonth as varchar)+' T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName='+CAST(@serverName as varchar)+' and CounterName=''Processes blocked''
)
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<='+ cast(@AlterIntervalTime as varchar)+'
order by CounterDateTime DESC'
exec (@strSql)
giftsf 2016-08-04
- 打赏
- 举报
declare @NowMonth int
declare @CounterName varchar(20)
declare @serverName varchar(500)
declare @AlterIntervalTime int
declare @strSql varchar(500)
select @serverName='cnshjssql14.ad4.ad.alcatel.com'
set @CounterName='Processes blocked'
set @AlterIntervalTime=30
set @NowMonth=month(GETDATE())
set @strSql=' select CounterID,CounterDateTime, CounterValue,@serverName from dbo.ASBSQL_PerformanceCounterDetailM+'+CAST(@NowMonth as varchar)+' T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName='''+@serverName+''' and CounterName=''Processes blocked''
)
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())>='+ cast(@AlterIntervalTime as varchar)+'
order by CounterDateTime desc'
print @strSql
exec (@strSql)
中国风 2016-08-04
- 打赏
- 举报
declare @NowMonth VARCHAR(2),@CounterName varchar(100)
set @NowMonth=month(GETDATE())
insert into #ASB_SQLCountValueInfo(BlockedCounterID,BlockedCounterDateTime,BlockedCounterValue,ServerName)
EXEC( 'SELECT CounterID,CounterDateTime, CounterValue,''cnjb01'' from dbo.ASBSQL_PerformanceCounterDetailM+'+@NowMonth+' T
where CounterID in (
select CounterID from [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
where ServerName=''cnjb01'' and CounterName=''Processes blocked''
)
and T.CounterValue>=20 and DATEDIFF(MINUTE,CounterDateTime,GETDATE())<=30
order by CounterDateTime DESC')
happy664618843 2016-08-04
- 打赏
- 举报
CounterName=Processes blocked改为[CounterName=Processes blocked][/quote]
不行
消息 137，级别 15，状态 2，第 1 行
必须声明标量变量 "@serverName"。
消息 4145，级别 15，状态 1，第 5 行
在应使用条件的上下文(在 ')' 附近)中指定了非布尔类型的表达式。
以学习为目的 2016-08-04
- 打赏
- 举报
CounterName=Processes blocked改为[CounterName=Processes blocked]
Ny-6000 2016-08-04
- 打赏
- 举报
动态表名要先拼接
这是必需的.
Ny-6000 2016-08-04
- 打赏
- 举报
以上的基础上,综合修改下就行了.
以学习为目的 2016-08-04
- 打赏
- 举报
动态表名要先拼接，然后exec('')或者 sp_exectuesql
加载更多回复（3）
