动态SQL，参数化查询
问题描述：
我写了一个查询，它提取SQL AlwaysON健康信息。剩下的唯一事情就是在最后添加一个where子句以过滤AvailabilityGroupName和DatabaseName。当我在查询中输入时：'select * from results where AvailabilityGroupName ='LAB-VIP-USADB'和DatabaseName ='CPS''。但是，当我将查询存储在变量nvarchar变量@sql中，然后执行存储在变量中的查询时，我收到错误消息“关键字'EXEC'附近的语法错误”。我想参数化查询的原因是一个可用性组可能包含多个数据库。参数化输入将来自PRTG，以便它查询该特定数据库的可用性组。动态SQL，参数化查询
DECLARE @sql NVARCHAR(MAX)
SET @sql='SELECT * FROM Results WHERE AvailabilityGroupName = ''LAB-VIP-USADB'' AND DatabaseName = ''CPS'''
--select @sql
;
WITH basicaginfo AS(
SELECT
ag.name AS AvailabilityGroupName,
cs.replica_server_name AS NodeName,
rs.role_desc,
rs.synchronization_health_desc,
DB_NAME(drs.database_id) AS DatabaseName
FROM
sys.availability_groups ag
JOIN
sys.dm_hadr_availability_replica_cluster_states cs on ag.group_id = cs.group_id
JOIN
sys.dm_hadr_availability_replica_states rs ON (ag.group_id=rs.group_id AND cs.replica_id = rs.replica_id)
JOIN
sys.dm_hadr_database_replica_states drs ON (ag.group_id=drs.group_id AND cs.replica_id = drs.replica_id)
\t \t \t \t \t),
AG_Stats AS
(
SELECT AR.replica_server_name,
HARS.role_desc,
Db_name(DRS.database_id) [DBName],
DRS.last_commit_time
FROM sys.dm_hadr_database_replica_states DRS
INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
),
Pri_CommitTime AS
(
SELECT replica_server_name
, DBName
, last_commit_time
FROM AG_Stats
WHERE role_desc = 'PRIMARY'
),
Sec_CommitTime AS
(
SELECT replica_server_name
, DBName
, last_commit_time
FROM AG_Stats
WHERE role_desc = 'SECONDARY'
),
\t \t Results AS
\t \t \t \t \t (
\t \t \t \t \t SELECT
\t \t \t \t \t \t AvailabilityGroupName,
\t \t \t \t \t \t DatabaseName,
\t \t \t \t \t \t [LAB-SCB-SQL01],
\t \t \t \t \t \t [LAB-SCB-SQL02],
\t \t \t \t \t \t [LAB-LAS-SQL01],
\t \t \t \t \t \t [LAB-LAS-SQL02],
\t \t \t \t \t \t [Max_Sync_Lag_Secs]
\t \t \t \t \t FROM(
\t \t \t \t \t \t \t SELECT
\t \t \t \t \t \t \t \t bb.AvailabilityGroupName,
\t \t \t \t \t \t \t \t bb.DatabaseName,
\t \t \t \t \t \t \t \t bb.NodeName,
\t \t \t \t \t \t \t \t bb.synchronization_health_desc,
\t \t \t \t \t \t \t \t MAX(DATEDIFF(ss,s.last_commit_time,p.last_commit_time)) OVER (PARTITION BY NULL) AS [Max_Sync_Lag_Secs]
\t \t \t \t \t \t \t FROM
\t \t \t \t \t \t \t \t basicaginfo bb
\t \t \t \t \t \t \t \t \t LEFT JOIN
\t \t \t \t \t \t \t \t Pri_CommitTime p ON p.DBName=bb.DatabaseName
\t \t \t \t \t \t \t \t \t LEFT JOIN
\t \t \t \t \t \t \t \t Sec_CommitTime s ON bb.NodeName = s.replica_server_name
\t \t \t \t \t \t) AS Data
\t \t \t \t \t PIVOT(
\t \t \t \t \t \t \t MAX(synchronization_health_desc) FOR [NodeName] IN([LAB-SCB-SQL01], [LAB-SCB-SQL02], [LAB-LAS-SQL01], [LAB-LAS-SQL02])
\t \t \t \t \t \t ) AS PivotedData
\t \t \t \t \t)
EXEC(@sql)
答
你不能做到这一点，CTE表只对下一个SQL命令进行访问。
当您在内部执行命令EXEC（@sql）时，SQL将执行多个命令。
尝试将所有代码移至变量。
DECLARE @sql NVARCHAR(MAX)
SET @sql=N'WITH basicaginfo AS(
SELECT
ag.name AS AvailabilityGroupName,
cs.replica_server_name AS NodeName,
rs.role_desc,
rs.synchronization_health_desc,
DB_NAME(drs.database_id) AS DatabaseName
FROM
sys.availability_groups ag
JOIN
sys.dm_hadr_availability_replica_cluster_states cs on ag.group_id = cs.group_id
JOIN
sys.dm_hadr_availability_replica_states rs ON (ag.group_id=rs.group_id AND cs.replica_id = rs.replica_id)
JOIN
sys.dm_hadr_database_replica_states drs ON (ag.group_id=drs.group_id AND cs.replica_id = drs.replica_id)
),
AG_Stats AS
(
SELECT AR.replica_server_name,
HARS.role_desc,
Db_name(DRS.database_id) [DBName],
DRS.last_commit_time
FROM sys.dm_hadr_database_replica_states DRS
INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
),
Pri_CommitTime AS
(
SELECT replica_server_name
, DBName
, last_commit_time
FROM AG_Stats
WHERE role_desc = ''PRIMARY''
),
Sec_CommitTime AS
(
SELECT replica_server_name
, DBName
, last_commit_time
FROM AG_Stats
WHERE role_desc = ''SECONDARY''
),
Results AS
(
SELECT
AvailabilityGroupName,
DatabaseName,
[LAB-SCB-SQL01],
[LAB-SCB-SQL02],
[LAB-LAS-SQL01],
[LAB-LAS-SQL02],
[Max_Sync_Lag_Secs]
FROM(
SELECT
bb.AvailabilityGroupName,
bb.DatabaseName,
bb.NodeName,
bb.synchronization_health_desc,
MAX(DATEDIFF(ss,s.last_commit_time,p.last_commit_time)) OVER (PARTITION BY NULL) AS [Max_Sync_Lag_Secs]
FROM
basicaginfo bb
LEFT JOIN
Pri_CommitTime p ON p.DBName=bb.DatabaseName
LEFT JOIN
Sec_CommitTime s ON bb.NodeName = s.replica_server_name
) AS Data
PIVOT(
MAX(synchronization_health_desc) FOR [NodeName] IN([LAB-SCB-SQL01], [LAB-SCB-SQL02], [LAB-LAS-SQL01], [LAB-LAS-SQL02])
) AS PivotedData
)
SELECT * FROM Results WHERE AvailabilityGroupName = ''LAB-VIP-USADB'' AND DatabaseName = ''CPS'''
EXEC(@sql)
我尝试了在我的环境中运行这个命令，我得到了错误，因为我没有它的表。
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.availability_groups'.