Dynamic SQL, parameterized Query

Question

I've written a query which pulls SQL AlwaysON health information. The only thing left to do is add a where clause in the end to filter the AvailabilityGroupName and DatabaseName. It works when I put in the query: 'select * from results where AvailabilityGroupName = 'LAB-VIP-USADB' AND DatabaseName = 'CPS''. However when I store the query in a nvarchar variable @sql in the beginning and later on execute the query stored in the variable, I received error message "Incorrect syntax near the keyword 'EXEC'". The reason why I want to paramerterized the query is that one availability group could contain multiple databases. The paramterized input will come from PRTG so that it queries the Availability group for that specific database.

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)
             

					),

   
       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
					)
EXEC(@sql)

Dynamic SQL, parameterized Query

Answers (1)

Related Questions