SQL Server EXEC @SQL command causing an error

Question

IF (SELECT COUNT(*) 
    FROM
        (SELECT [Domain], [Server], [Instance], [DatabaseName] 
         FROM [dbo].[OF_Databases_A]
         INTERSECT 
         SELECT [Domain], [Server], [Instance], [DatabaseName] 
         FROM [dbo].[OF_Databases]) z) > 0
BEGIN
    DECLARE @SQL Nvarchar(max)

    SET @SQL=  
    (
    select 'Delete from [dbo].[OF_Databases] where domain='''+[Domain]+'''' + ' and server= '''+[Server] +'''' + ' and instance= '''+[Instance] +'''' + ' and DatabaseName= '''+[DatabaseName] +''''  + ' GO' from [dbo].[OF_Databases_A] 
    INTERSECT 
    select 'Delete from [dbo].[OF_Databases] where domain='''+[Domain]+'''' + ' and server= '''+[Server] +'''' + ' and instance= '''+[Instance] +'''' + ' and DatabaseName= '''+[DatabaseName] +''''  + ' GO'  from [dbo].[OF_Databases] 
    ) 

    EXECUTE @SQL
END

If I execute this query, I get the following error:

Msg 512, Level 16, State 1, Line 81
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Msg 2812, Level 16, State 62, Line 87
Could not find stored procedure ''.

How can I fix this?

Answer 1

Your variable could take only one value, you are giving it a resultSet, for it to work you need to do like this:

IF (SELECT COUNT(*) 
    FROM
        (SELECT [Domain], [Server], [Instance], [DatabaseName] 
         FROM [dbo].[OF_Databases_A]
         INTERSECT 
         SELECT [Domain], [Server], [Instance], [DatabaseName] 
         FROM [dbo].[OF_Databases]) z) > 0
BEGIN
    DECLARE @SQL Nvarchar(max) = ''

    SELECT  @SQL = @SQL + ISNULL(QUERY, '') + ' '
FROM 
    (
    select 'Delete from [dbo].[OF_Databases] where domain='''+[Domain]+'''' + ' and server= '''+[Server] +'''' + ' and instance= '''+[Instance] +'''' + ' and DatabaseName= '''+[DatabaseName] +''''  + ' GO' AS QUERY from [dbo].[OF_Databases_A] 
        INTERSECT 
        select 'Delete from [dbo].[OF_Databases] where domain='''+[Domain]+'''' + ' and server= '''+[Server] +'''' + ' and instance= '''+[Instance] +'''' + ' and DatabaseName= '''+[DatabaseName] +''''  + ' GO'  from [dbo].[OF_Databases] 
        ) a

    EXECUTE (@SQL)
END