SQL Server EXEC @SQL command causing an error-CodePudding

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?

CodePudding user response：

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